Excel VBA: Last Used Row

If you’re getting into Excel macros and have fooled around with the macro recorder you may notice that the resulting code is very rigid: it only works on the exact range of cells you had selected for example (among other drawbacks). In many cases you want your code to adapt to the size of the data in a sheet, and for this you need to determine the boundaries of the range.

.UsedRange Property

If you’ve done a search you may have come across this approach already. .UsedRange.Rows.Count will usually give the right result if you’re looking for the last used row on a particular sheet, but not always. For whatever reason, this property is not always current – sometimes it is possible to clear the contents of a row without causing this to update, giving an incorrect result. Saving the document (and probably some other actions) will cause this to be updated, but I don’t consider it to be reliable enough to base further logic on.

.End(xlUp).row

This approach is the equivalent of putting your cursor in a cell and pushing CTRL+UP. If you do this on the very last row of a sheet, the cell that your cursor lands on will be the last one in that column. This works reliably, but only gives a result for one column, not the entire sheet. If you put this together with a loop and repeat it for all columns (or a reasonable number) you can reliably retrieve the last used row on a sheet.

The function: getLastRow()

Putting this all together, an easy to use function can be made that returns the last row number on a sheet or in a specific column.

• If called with no parameters it will return the last used row in the first 100 columns of the active sheet.
• The optional sheetName parameter is useful to reference a sheet other than the active one, or if you can’t guarantee which sheet is active when the function is called.
• The second optional parameter, colNum, can be used if you want to know the last used row in a specific column.

Examples

Here are some examples of how this works with the sample sheet below:

getLastRow() = 8 'from the active sheet
getLastRow("Fruit") = 8 'even when a different sheet is active
getLastRow(,3) = 6 'from the active sheet
getLastRow("Fruit", 3) = 6 'even when a different sheet is active
getLastRow("Fruit", 4) = 1 'this column is empty
getLastROW("Vegetables") = 0 'this sheet name is invalid

And finally, the function itself:

Function getLastRow(Optional sheetName As String, Optional colNum As Long) As Long
'by Elliot 7/22/20 www.elliotmade.com
'this function will return the last used row on a sheet or a single row
'if no sheet name is specified it will use the active sheet
'if no column is specified it will find the last row in any of the first 100 columns
'two assumptions are made: the file type is .xlsx or similar that supports ~1M rows
'and the sheet is in the active workbook

'a zero returned means that there was a failure

Dim i As Long
Dim j As Long
Dim curLastRow As Long

'check for valid inputs first, return zero if there is a problem
If colNum < 0 Or colNum > 16384 Then GoTo abort

If sheetName <> "" Then
    For j = 1 To Worksheets.Count
        If Worksheets(j).Name = sheetName Then GoTo sheetOK
    Next j
    GoTo abort 'specified sheet name was not found
sheetOK:
End If

If sheetName = "" Then sheetName = ActiveSheet.Name

'if no problem, find the last row
If colNum = 0 Then
For i = 1 To 100
    curLastRow = Worksheets(sheetName).Cells(1048575, i).End(xlUp).Row
    If curLastRow > getLastRow Then getLastRow = curLastRow
Next i

Else
    curLastRow = Worksheets(sheetName).Cells(1048575, colNum).End(xlUp).Row
End If

abort:

getLastRow = curLastRow

End Function

If you found this useful, or if you have a problem this might help you solve, let me know!