Count Rows in Excel VBA

There are a few methods to count rows and each of them uses VBA. In this example, I’ll show two of them.

Range.End

This is the simplest way to count a number of rows on the worksheet, but at the same time, this method is not very universal. This method only works if there is a perfect table – you have the same amount of data in each column.

Because each column has the same amount of rows, we can use any of them to show how the code works. I’m going to take the first column A.

Open VBA Editor by using Alt + F11 and enter the following code.

Code explanation

2. Declare variable first_row as Long,
3. Rows.Count is the number of rows in an Excel worksheet (just over one million). Now the application checks each value, from the last row, by moving up,
4. If it meets the first row with a value it prints it inside a message box.

Range.Find

This is a little different way of counting the number of rows inside a worksheet. This method also works with the “uglier” set of data inside our table.

Now, if you run the code you will get 6, which is not what we want.

Let’s try another code. This time it will use Range.Find, instead of Range.End.

This time the code returns the following window.

Code explanation

3. This line of code is looking for any data (What:=”*”). The xlPrevious means that Excel starts searching for a value in the very last Excel cell and then it moves from right to left from the last row to the first until it reaches the non-blank cell.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.