Find Last Row

[detriez]

I have multiple Subs that require finding the last row of my sheet


I use this over and over

Dim lr As Long
    lr = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row + 1
    Application.ScreenUpdating = False

I've added this module. How would I replace the above with this function?

Private Sub sLastRow()

Dim Last_Row As Long
   
On Error Resume Next
Last_Row = Cells.Find(What:="*", _
                After:=Range("A1"), _
                LookIn:=xlFormulas, _
                LookAt:=xlPart, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious).Row
On Error GoTo 0
   
Debug.Print Last_Row

End Sub

 

[Fluff]

How about

Function sLastRow() As Long

Dim Last_Row As Long
   
On Error Resume Next
Last_Row = Cells.Find(What:="*", _
                After:=Range("A1"), _
                LookIn:=xlFormulas, _
                LookAt:=xlPart, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious).Row
On Error GoTo 0
   
If Last_Row = 0 Then sLastRow = 1 Else sLastRow = Last_Row

End Function
Sub chk2()
   Dim Lr As Long
   Lr = sLastRow
   Debug.Print Lr
End Sub

 

[dmt32]

Try

Function LastRow(Optional ByVal sh As Object) As Long
    If sh Is Nothing Then Set sh = ActiveSheet
    On Error Resume Next
    LastRow = sh.Cells.Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, _
                            SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    On Error GoTo 0
End Function

  Dim lr As Long
    lr = LastRow(ActiveSheet) + 1

 

[detriez]

Thanks all.
Fluff. Worked as expected

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[detriez]

removed