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

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.

CodePudding user response：

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