You can use the following basic syntax in VBA to find the last row used in an Excel sheet:
Sub FindLastRow()
Range("D2")=Cells.Find("*",Range("A1"),xlFormulas,xlPart,xlByRows,xlPrevious,False).Row
End Sub
This particular example finds the last used row in the current sheet and returns the result in cell D2.
If you would instead like to display the last row in a message box, you can use the following syntax:
Sub FindLastRow()
Dim LastRow As Long
LastRow=Cells.Find("*", Range("A1"),xlFormulas,xlPart,xlByRows,xlPrevious,False).Row
MsgBox "Last Row: " & LastRow
End Sub
The following examples shows how to use each of these methods in practice.
Example 1: Find Last Row Using VBA and Display Results in Cell
Suppose we have the following dataset in Excel that contains information about various basketball players:
We can create the following macro to find the last row used in this Excel sheet and display the result in cell D2:
Sub FindLastRow()
Range("D2")=Cells.Find("*",Range("A1"),xlFormulas,xlPart,xlByRows,xlPrevious,False).Row
End Sub
When we run this macro, we receive the following output:
Notice that cell D2 contains a value of 11.
This tells us that the last used row in this particular sheet is row 11.
It’s also worth noting that if you have empty cells before a used cell, this macro will still find the last used row.
For example, suppose we run the macro on the following dataset:
Cell D2 contains a value of 16 because this is the last row with a value in it.
Example 2: Find Last Row Using VBA and Display Results in Message Box
Suppose we would instead like to find the last used row in a sheet and display the row number in a message box.
We can create the following macro to do so:
Sub FindLastRow()
Dim LastRow As Long
LastRow=Cells.Find("*", Range("A1"),xlFormulas,xlPart,xlByRows,xlPrevious,False).Row
MsgBox "Last Row: " & LastRow
End Sub
When we run this macro, we receive the following output:
The message box tells us that the last used row in the sheet is row 11.
Note: You can find the complete documentation for the VBA Find method here.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
VBA: How to Calculate Average Value of Range
VBA: How to Count Number of Rows in Range
VBA: How to Remove Duplicate Values in Range
