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