returns the (actual) last row of the current Excel Sheet

endOfSheet.bas

Function endOfSheet(Optional targetSheet As Worksheet) As Long
    '' returns the (actual) last row of the current sheet
    ' this is far better than using '65536' or whatever the current row size might be,
    ' especially since .xlsx has 2^20 (1048576), not 2^16.
    If targetSheet Is Nothing Then
        Set targetSheet = ActiveWorkbook.ActiveSheet
    End If
    endOfSheet = targetSheet.Rows.Count
End Function

lastUsedRow.bas

Function lastUsedRow(Optional targetSheet As Worksheet) As Long
    '' returns the last used row in the current worksheet
    ' for columns A:E in this example -- change Range below as needed
    ' dependencies: endOfSheet() -- actual last row of current sheet
    If targetSheet Is Nothing Then
        Set targetSheet = ActiveWorkbook.ActiveSheet
    End If
    lastUsedRow = targetSheet.Range("A" & endOfSheet(targetSheet)).End(xlUp).Row
End Function