To get the last row in Excel VBA, you can use the following code:
Dim lastRow as Long
lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
The above code uses the Cells property and the End method to find the last used row in column 1 (A).
Here’s a breakdown of the code:
ActiveSheetrefers to the currently active sheet in Excel.Rows.Countreturns the total number of rows in the sheet..End(xlUp)moves to the last cell containing data in column 1 by starting from the last row and moving upwards..Rowreturns the row number of the last cell found.- The value of
lastRowwill be the row number of the last used row in column 1.
Here’s an example to better understand the concept:
| Column A | Column B |
|---|---|
| 1 | Value A1 |
| 2 | Value A2 |
| 3 | Value A3 |
| 4 | Value A4 |
In this example, the code will return 4 as the last row because the fourth row in column 1 contains the last used cell.