VBA : How to Find Last Visible Row

Yash Gaur Add Comment

This tutorial explains how to find last visible row using VBA code in Excel.

You can download the following dataset to practice.

1. Using SpecialCells method

The SpecialCells method with the xlCellTypeVisible argument allows us to identify visible cells in a filtered range or after rows have been hidden.

The following code can be used to identify the last visible row in the dataset :

Sub VisibleRow()
    Dim lastRow As Long
    Dim ws As Worksheet
    Dim visibleCells As Range
    Dim cell As Range
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    On Error Resume Next
    Set visibleCells = ws.Range("A1:A" & lastRow).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    
    If Not visibleCells Is Nothing Then
        
        For Each cell In visibleCells.Cells
            lastRow = cell.Row
        Next cell
        
        MsgBox "The last visible row is: " & lastRow
    Else
        MsgBox "No visible rows found."
    End If
End Sub
Press Run or F5 to run the above macro.
VBA : Last Visible Row using Special Cells Method
The .SpecialCells(xlCellTypeVisible) method excludes any cells that are hidden due to filtering, manual hiding or grouping.
2. Use EntireRow.Hidden Property

The EntireRow.Hidden property is used to hide/unhide a row. When using with If statement it can also be used to determine if a row is hidden or not.

The following code finds the last visible row in column "A" by checking from the last non-empty row upward and then we identify the first non-hidden row. If a visible row is found, it shows its row number in a message box.

Sub LastVisibleRow()
    Dim lastRow As Long
    Dim ws As Worksheet
    Dim cell As Range
    Dim i As Integer
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    For i = lastRow To 1 Step -1
        Set cell = ws.Cells(i, "A")
        
        If cell.EntireRow.Hidden = False Then
            MsgBox "The last visible row is: " & i
            Exit Sub
        End If
    Next i
    
    MsgBox "No visible rows found."
End Sub
Related Posts
Spread the Word!
Share
About Author:
Yash Gaur

Yash is pursuing an MBA in Finance with a keen interest in analytics. He enjoys working with data and leveraging his research and analytical skills to generate valuable insights.

Post Comment 0 Response to "VBA : How to Find Last Visible Row"
Next → ← Prev