vba-find-the-last-row-on-a-worksheet.vb

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'INPUT       : Sheet, the worksheet we'll search to find the last row
'OUTPUT      : Long, the last occupied row
'SPECIAL CASE: if Sheet is empty, return 1
'EXAMPLES    :
' 
'assume that there is a single 
'entry on MySheet in cell A5:
'
'LastRowNum(MySheet)
'>> 5
'
'assume that EmptySheet is totally empty:
'
'LastRowNum(EmptySheet)
'>> 1
'
Public Function LastRowNum(Sheet As Worksheet) As Long
    If Application.WorksheetFunction.CountA(Sheet.Cells) <> 0 Then
        LastRowNum = Sheet.Cells.Find(What:="*", _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious).Row
    Else
        LastRowNum = 1
    End If
End Function