There are a few methods to count rows and each of them uses VBA. In this example, I’ll show two of them.
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 work. I’m going to take the first column A.
Open VBA Editor by using Alt + F11 and enter the following code.
Sub CountRows1() Dim last_row As Long last_row = Cells(Rows.Count, 1).End(xlUp).Row MsgBox (last_row) End Sub
2. Declare variable first_row as Long,
3. Rows.Count is the number of rows in Excel worksheet (just over one million). Now the application checks each value, from the last row, by moving up,
4. If it met the first row with a value it prints it inside a message box.
This is a little different way of counting the number of rows inside a worksheet. This method also works with “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.
Sub CountRows1() Dim last_row As Long last_row = Cells.Find(What:="*", SearchDirection:=xlPrevious).Row MsgBox (last_row) End Sub
This time the code returns the following window.
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 move from right to left from the last row to the first until it reaches non-blank cell.