In order to count rows in Excel, you have to use the VBA code.
Open VBA Editor with Alt + F11.
We are going to use the following example.
Get the last row in the current column
Here, we will return the last cell with the selected column A.
1 2 3 |
Sub CountNumberOfRows() rowsInColumn = Cells(Rows.Count, 1).End(xlUp).Row End Sub |
The following code is going to return 4 because A4 is the last cell in column A.
Get the last row in the current sheet
This code is a bit different. It’s going to return 6 because B6 is the last row in all columns (entire sheet). The following code is the safest as it takes into consideration previously deleted cells.
1 2 3 |
Sub CountNumberOfRows() numberOfRows = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row End Sub |