Count number of rows in Excel

In order to count rows in Excel, you have to use 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.

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.

Sub CountNumberOfRows()
    numberOfRows = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
End Sub

Posted in vba