Convert find LastRow code into a function-CodePudding

I am using this code a lot of times to get the last row number and it works correctly,

Dim sh As Worksheet: Set sh = ActiveSheet
Dim lastRow As Long
lastRow = sh.Cells.Find("*", LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

I need to convert it into a function, I tried the following but the output is always 0 with no error raised.

Function LR(sh As Worksheet) As Long
    Dim lastRow As Long, LastColumn As Long
    lastRow = sh.Cells.Find("*", LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End Function

And using like this

Dim i As Long: i = LR(ActiveSheet)
Debug.Print i

CodePudding user response：

Your function never returns anything.

You need to add LR = lastRow if you want it to return the value.

Or just return it like this (since you aren't using LastColumn anyway):

Function LR(sh As Worksheet) As Long
   LR = sh.Cells.Find("*", LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End Function

CodePudding user response：

Last Row Function

If the worksheet is empty, the result will be 0 (the initial value of LR).

Sub LRtest()
    Debug.Print LR(ThisWorkbook.Worksheets("Sheet1"))
End Sub

Function LR(ByVal ws As Worksheet) As Long
    ' Since this will fail if the worksheet is filtered, you could add...
    'If ws.FilterMode Then ws.ShowAllData ' ... to remove any filters.
    Dim lCell As Range
    Set lCell = ws.UsedRange.Find("*", , xlFormulas, , xlByRows, xlPrevious)
    If Not lCell Is Nothing Then LR = lCell.Row
End Function