FLASH SALE! - "CHATGPT AND ARTIFICIAL INTELLIGENCE FOR MICROSOFT EXCEL AT 60% OFF" Enroll Now

VBA Row Count

Updated on June 12, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Excel VBA Row Count

In VBA programming, referring to rows is most important as well, and counting them is one thing you must be aware of when it comes to VBA coding. We can get a lot of value if we understand the importance of counting rows with data in the worksheet. This article will show you how to count rows using VBA coding.

VBA Row Count

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Row Count (wallstreetmojo.com)

How to Count Rows in VBA?

You can download this VBA Row Count Excel Template here – VBA Row Count Excel Template

Example #1

To count rows, we need to use the RANGE object. In this object, we need to use the ROWS object. In this, we need to use the COUNT property.

Look at the below data in Excel.

VBA Row Count Example 1

From the above data, we need to identify how many rows are there from the range A1 to A8. So first, define the variable as an Integer to store the number of rows.

Code:

Sub Count_Rows_Example1()

Dim No_Of_Rows As Integer

End Sub
VBA Row Count Example 1-1

We will assign row numbers for this variable, so enter the variable name and the equal sign.

Code:

Sub Count_Rows_Example1()

Dim No_Of_Rows As Integer

No_Of_Rows =

End Sub
VBA Row Count Example 1-2

We need to provide a range of cells, so open the RANGE object and supply the range as “A1:A8”. 

Code:

Sub Count_Rows_Example1()

Dim No_Of_Rows As Integer

No_Of_Rows = Range("A1:A8")

End Sub
VBA Row Count Example 1-3

Once we supply the range, we need to count the number of rows, so choose the ROWS property of the RANGE object.

VBA Row Count Example 1-4

We are counting several rows in the RANGE object’s ROWS property, so choose the “COUNT” property now.

VBA Row Count Example 1-7

Now in the message box, show the value of the variable.

Code:

Sub Count_Rows_Example1()

Dim No_Of_Rows As Integer

No_Of_Rows = Range("A1:A8").Rows.Count

MsgBox No_Of_Rows

End Sub
VBA Row Count Example 1-5

Now, run the code and see the count of rows of the supplied range of cells.

VBA Row Count Example 1-6

There are 8 rows supplied for the range, so the row count is 8 in the message box.

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

Example #2

We have other ways of counting rows as well. For the above method, we need to supply a range of cells, showing the number of rows selected.

But imagine the scenario where we need to find the last use of any column. For example, take the same data as seen above.

VBA Row Count Example 1

To move to the last used cell from cell A1, we press the shortcut excel key “Ctrl + Down Arrow,” so it will take you to the last cell before the empty cell.

First, supply the cell as A1 using the RANGE object.

Code:

Sub Count_Rows_Example2()

Dim No_Of_Rows As Integer

No_Of_Rows = Range("A1")

MsgBox No_Of_Rows

End Sub
VBA Row Count Example 2

From this cell, we need to move down. We use Ctrl + Down Arrow in the worksheet, but in VBA, we use the END property. Choose this property and open the bracket to see options.

Example 2-1

Look there with the END key. We can see all the arrow keys like “xlDown, xlToLeft, xlToRight, and xlUp” since we need to move down and use the “xlDown” option.

Code:

Sub Count_Rows_Example2()

Dim No_Of_Rows As Integer

No_Of_Rows = Range("A1").End(xlDown)

MsgBox No_Of_Rows

End Sub
Example 2-2

It will take you to the last cell before any break. We need the row number in the active cell so use the ROW property.

Code:

Sub Count_Rows_Example2()

Dim No_Of_Rows As Integer

No_Of_Rows = Range("A1").End(xlDown).Row

MsgBox No_Of_Rows

End Sub
Example 2-3

Now, this will show the last row number, which will be the count of the number of rows.

VBA Row Count Example 2-4

So in rows, we have data.

Example #3 – Find Last Used Row

Finding the last used row is important to decide how many times the loop has to run. Also, in the above method, the last row stops to select if there is any breakpoint cell. So in this method, we can find the last used row without any problems.

Open CELL property.

Code:

Sub Count_Rows_Example3()

Dim No_Of_Rows As Integer

No_Of_Rows = Cells(

MsgBox No_Of_Rows

End Sub
VBA Row Count Example 3

Now, we need to mention the row number to start with. The problem here is we are not sure how many rows of data we have so that we can go straight to the last row of the worksheet, for this mention, ROWS.COUNT property.

Code:

Sub Count_Rows_Example3()

Dim No_Of_Rows As Integer

No_Of_Rows = Cells(Rows.Count,

MsgBox No_Of_Rows

End Sub
Example 3-1

Next, we need to mention in which column we are finding the last used row, so in this case, we are finding it in the first column, so mention 1.

Code:

Sub Count_Rows_Example3()

Dim No_Of_Rows As Integer

No_Of_Rows = Cells(Rows.Count, 1)

MsgBox No_Of_Rows

End Sub
Example 3-2

At this moment, it will take you to the last cell of the first column. We need to move upwards to the last used cell from there onwards, so use the End(xlUp) property.

Code:

Sub Count_Rows_Example3()

Dim No_Of_Rows As Integer

No_Of_Rows = Cells(Rows.Count, 1).End(xlUp)

MsgBox No_Of_Rows

End Sub
Example 3-3

So, this will take you to the last used cell of column 1, and in this cell, we need the row number, so use the ROW property to get the row number.

Code:

Sub Count_Rows_Example3()

Dim No_Of_Rows As Integer

No_Of_Rows = Cells(Rows.Count, 1).End(xlUp).Row

MsgBox No_Of_Rows

End Sub
VBA Row Count Example 3-4

Things to Remember

  • The COUNT will give several rows in the worksheet.
  • If you have a range, then it will give several rows selected in the range.
  • The ROW property will return the active cell row number.

This article has been a guide to VBA Row Count. Here, we discuss how to count used rows in Excel using VBA coding, practical examples, and a downloadable Excel template. You may learn more about Excel from the following articles: –