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.
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
Share Share Tweet