Convert find LastRow code into a function

Question

I use this code to get the last row number.

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.

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

Further readings at SO: [Error in finding last used cell in Excel with VBA](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba/11169920#11169920) — T.M., Jun 01 '22 at 09:22

score 2 · Accepted Answer · answered Jun 01 '22 at 09:18

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

score 1 · Answer 2 · answered Jun 01 '22 at 09:30

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

Convert find LastRow code into a function

2 Answers2

Last Row Function