How to know the last row with data, no matter if the row is hidden or not

Question

until now, to know which is the last row with data I use this code:

Function GetPrimeraFilaLibre(paramNombreHoja As String, paramColumnaReferencia As String)
    GetPrimeraFilaLibre = Sheets(paramNombreHoja).Range(paramColumnaReferencia & "65536").End(xlUp).Offset(1, 0).Row
End Function

This function take as parameter the sheet that I want to know the last row and as second parameter the column of reference that I will check if it has data or not.

The problem that this function doesn't work if there is hidden rows. I would like to know the last row with data, no matter if it is hidden or not.

Thanks.

Look at https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba. If your data doesn't contain emtpy rows, using `CurrentRegion` might be the easiest solution — FunThomas, Mar 30 '22 at 08:05

TinMan · Accepted Answer · 2022-03-30T10:30:48.707

Range.Find can be used to return the last row whether or not there are hidden rows.

Function GetPrimeraFilaLibre(paramNombreHoja As String, paramColumnaReferencia As String) As Long
    With Sheets(paramNombreHoja).Columns(paramColumnaReferencia)
        GetPrimeraFilaLibre = .Find(What:="*", After:=.Cells(1, 1), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
    End With
End Function

Function GetLastRow(SheetName As String, ColumnNameOrIndex As String) As Long
    With Sheets(SheetName).Columns(ColumnNameOrIndex)
        GetLastRow = .Find(What:="*", After:=.Cells(1, 1), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
    End With
End Function

Addendum

As VBasic2008 pointed out, we would need to make an adjustment to handle MergedCells.

Function GetLastRow(SheetName As String, ColumnNameOrIndex As String) As Long
    Dim Target As Range
    With Sheets(SheetName).Columns(ColumnNameOrIndex)
        Set Target = .Find(What:="*", After:=.Cells(1, 1), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
    End With
    GetLastRow = Target.MergeArea.Rows.Count + Target.Row - 1
End Function

I mostly agree with this and most often use this way but there are some 'special' cases. You know how `SpecialCells` treats visible cells equally even if they are visible by `AutoFilter`. Unfortunately, the `Find` method distinguishes these two cases and fails to find hidden cells in a filtered range (worksheet). Also, it is unreliable with merged cells. Worth pointing out is that the `xlFormulas` parameter 'makes it see' the hidden cells. So one could conclude that there is no universal way to find the last row. Other notable methods are by using `End`, `CurrentRegion`, `UsedRange`, `CountA`. — VBasic2008, Mar 30 '22 at 09:57
@VBasic2008 I prefer `Range.CurrentRegion` but it is hard to recommend t without knowing the exact use case. It's a shame that we cannot have a `ListObject` variable without converting the range to a `ListObject`. How nice would it be if `Range` had an `AsListObject` method. e.g. `Set Table = Range.CurrentRegion.AsListObject`. — TinMan, Mar 30 '22 at 10:25

How to know the last row with data, no matter if the row is hidden or not

1 Answers1

Addendum