This tutorial explains how to find last visible row using VBA code in Excel.
You can download the following dataset to practice.
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.
The .SpecialCells(xlCellTypeVisible) method excludes any cells that are hidden due to filtering, manual hiding or grouping.
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
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.
Share Share Tweet