Find Last Row With Excel VBA

Objective:

The goal is to identify the last row in a worksheet that contains data, regardless of whether the data is in column A, B, or any other column.

Code Example: Find Last Row Using VBA

Sub FindLastRow()
    Dim ws As Worksheet
    Dim lastRow As Long   
    ' Set the worksheet object (you can specify the sheet by name here)
    Set ws = ThisWorkbook.Sheets("Sheet1")  
    ' Method 1: Using Cells with xlUp (more reliable)
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 
    ' Display the last row in the Immediate Window (Ctrl + G to view)
    Debug.Print "Last row with data in column A: " & lastRow  
    ' You can also use this value for further processing, e.g., selecting or highlighting the last row
    ws.Rows(lastRow).Select
End Sub

Explanation:

1. Declaring the variables:
   • ws is a variable of type Worksheet. We will use this to refer to the specific worksheet where we want to find the last row.
   • lastRow is a Long variable that will store the row number of the last row that contains data.
2. Setting the Worksheet:
3. Set ws = ThisWorkbook.Sheets(« Sheet1 »)

This line sets the ws variable to reference the worksheet named « Sheet1 ». You can replace « Sheet1 » with the name of the sheet you want to work with.

1. Finding the Last Row:
2. lastRow = ws.Cells(ws.Rows.Count, « A »).End(xlUp).Row

This is the core of the solution. Let’s break it down:

1. • ws.Cells: This refers to all the cells in the worksheet ws.
   • ws.Rows.Count: This gives the total number of rows available in the worksheet (which, in most Excel versions, is 1,048,576 rows).
   • « A »: We are checking column A in this case. You can change the letter to another column if your data is located elsewhere.
   • .End(xlUp): This is equivalent to pressing Ctrl + ↑ on the keyboard in Excel. It will move from the bottom of the worksheet (the last row) upwards until it finds a non-empty cell. Essentially, this is how we find the last used row in the column.
   • .Row: This returns the row number of the cell that was found by the .End(xlUp) method.

Why use xlUp instead of xlDown or other methods?

1. • xlUp is typically more reliable than xlDown. If there are any blank rows after the last row with data, using xlDown would cause incorrect results, as it would consider the first empty row as the « last row. » xlUp, however, ensures that we start from the bottom of the worksheet and work upwards, guaranteeing that we find the last non-empty row.
2. Output to the Immediate Window:
3. Debug.Print « Last row with data in column A:  » & lastRow

This line outputs the value of lastRow to the Immediate Window (which you can access by pressing Ctrl + G). This is useful for debugging and verifying the result. You can remove this line if you don’t need the debug output.

1. Selecting the Last Row:
2. ws.Rows(lastRow).Select

This line selects the entire last row that contains data. It’s an optional line, but you can use it to highlight the row in Excel for further action.

Alternative Method: Using UsedRange

Another way to find the last row is by using the UsedRange property, which gives you the range of cells that have data. While this can work, it’s less reliable in certain situations (e.g., when there are non-contiguous ranges of data).

Sub FindLastRowUsingUsedRange()
    Dim ws As Worksheet
    Dim lastRow As Long  
    ' Set the worksheet object (you can specify the sheet by name here)
    Set ws = ThisWorkbook.Sheets("Sheet1")
    ' Method 2: Using UsedRange (may not work in all cases, especially with empty rows)
    lastRow = ws.UsedRange.Rows(ws.UsedRange.Rows.Count).Row   
    ' Output to Immediate Window
    Debug.Print "Last row with data using UsedRange: " & lastRow
End Sub

Note: The UsedRange property returns the range of cells that have ever contained data, so if you’ve previously deleted rows or columns, it may include those as part of the « used » range. This can result in inaccurate results in some cases.

When to Use Each Method:

• Use xlUp from the last row when you want the most reliable result, especially when your data might have gaps or blank rows between data.
• Use UsedRange if you’re working with a small sheet and are confident there won’t be any gaps or if you need the range of all cells that have ever contained data, even if they’ve been cleared.

Conclusion:

The method of finding the last row using .End(xlUp) is the most reliable, especially when working with data that might not fill every row continuously. It’s fast and efficient, and once you understand how to use it, it will be a core part of your Excel VBA toolkit for managing data.