WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Row Count

VBA Row Count

Excel VBA Row Count

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

VBA Row Count

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 make use of RANGE object, in this object, we need to use the ROWS object, and in this, we need to use COUNT property.

For example, 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 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

For this variable, we will assign row numbers, so enter the variable name and out 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 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 the range is supplied we need to count the number of rows, so choose ROWS property of RANGE object.

VBA Row Count Example 1-4

In ROWS property of RANGE object we are counting a number of rows, so choose “COUNT” property now.

VBA Row Count Example 1-7

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

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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 supplied range of cells.

VBA Row Count Example 1-6

Ok, there are 8 rows supplied for the range, so rows count is 8 in the message box.

Example #2

We have other ways of counting rows as well, for the above method, we need to supply a range of cells, and in this range cells, it shows the number of rows selected.

But imagine the scenario where we need to find the last used 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, and in the worksheet, we use Ctrl + Down Arrow, but in VBA we use END property, choose this property and open bracket to see options.

Example 2-1

Look there with END key we can see all the arrow keys like “xlDown, xlToLeft, xlToRight, and xlUp” since we need to move down use “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

This will take you to the last cell before any break, now in the active cell we need the row number, so use 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

Ok, done. Now, this will show the last row number, and that will be the count of a 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 so important to decide how many times the loop has to run and 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 what we can do is we straight away go 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 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, from there onwards we need to move upwards to go the last used cell, so use 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 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

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

Recommended Articles

This has been a guide to VBA Row Count. Here we discuss how to count used rows in excel using VBA Coding along with practical examples and downloadable excel template. You may learn more about excel from the following articles-

  • VBA Insert Row
  • VBA Delete Row
  • VBA StatusBar
  • VBA Variable Range
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Row Count Excel Template

New Year Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More