How to find the last used row in Excel VBA
There are multiple ways to find the last used row in Excel using VBA. Here are a few examples:
Example 1: Using the End property
Dim lastRow As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
In this example, we use the End property to find the last used row in column A. The Rows.Count property returns the total number of rows in the worksheet, and xlUp is used to navigate upwards from the last row. The “Row” property retrieves the row number of the last used cell in column A.
Example 2: Using the Find method
Dim lastRow As Long
lastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
In this example, we use the Find method to search for the last non-empty cell in the worksheet. The “*” argument is used to find any non-empty cell, and xlByRows specifies that the search should be performed row-wise. xlPrevious ensures that the search starts from the last cell and moves in reverse order. Finally, the “Row” property retrieves the row number of the last non-empty cell.
Example 3: Using the UsedRange property
Dim lastRow As Long
lastRow = ActiveSheet.UsedRange.Rows.Count
In this example, we use the UsedRange property to get the range of cells that are currently used in the worksheet. The “Rows.Count” property retrieves the total number of rows in the UsedRange, giving us the last used row in the worksheet.
These are just a few examples of how to find the last used row in Excel using VBA. Depending on your specific requirements, you can choose the method that best suits your needs.