I would like to say that I wrote this little function, but I did not. I don’t even recall how or where I found it. But, this is the one handy piece of code for VBA. The function simply returns the row number of the last used row on a worksheet. With dynamic worksheets, where the rows are added and deleted by the user or by code, this is a well worn tool for finding where the data stop.

I did add the parameter (ws as Worksheet) so that I could use the function from a Main module to run against multiple worksheets.

Public Function FindLastRow(ws As Worksheet) As Integer
' Find the last used Row on a Worksheet
On Error Resume Next

    If WorksheetFunction.CountA(ws.Cells) > 0 Then
        ' Search for any entry, by searching backwards by Rows
        FindLastRow = ws.Cells.Find(What:="*", After:=ws.Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End If

End Function

The first line of the If statement…

If WorksheetFunction.CountA(ws.Cells) > 0

…checks to see if there are any used cells on the worksheet. If there are the next line of code runs…

FindLastRow = ws.Cells.Find(What:="*", After:=ws.Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

…this code uses Excel’s Find method to look for any (“*”) data row by row starting with cell A1. This method will return the row number of the last used row. The return value of the function (Integer) is that number.

I have used the function to locate the last row of user input in statements like:

lngLastRow = FindLastRow(Sheet1) - 4

The only change that one might make is to return 0 if the result of WorksheetFunction.CountA is less than or equal to 0. I haven’t had a need for this, but it might be wise for error handling of sorts. However, you could see in the code above if the return value was 0 lngLastRow would be -4.

Public Function FindLastRow(ws As Worksheet) As Integer
' Find the last used Row on a Worksheet
On Error Resume Next

    If WorksheetFunction.CountA(ws.Cells) > 0 Then
        ' Search for any entry, by searching backwards by Rows
        FindLastRow = ws.Cells.Find(What:="*", After:=ws.Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Else
        FindLastRow = 0
    End If

End Function