Home ➜ VBA ➜ Count Rows using VBA in Excel
To count rows using VBA, you need to define the range from which you want to count the rows and then use the count and rows property to get the count of the row from that range. You can also use a loop to count rows where you have data only.
Use VBA to Count Rows
- First, you need to define the range for which you want to count the rows.
- After that, use a dot (.) to open the list of properties and methods.
- Next, type or select the “Rows” property.
- In the end, use the “Count” property.
Now when you run this code, it will return the count of the rows, and to get the count you can use a message box or directly enter that value into a cell as well.
Sub vba_count_rows()
Range("A1:A10").Rows.Count
End Sub
Count Rows for the Used Range
Sub vba_count_rows2()
MsgBox Worksheets("Sheet1").UsedRange.Rows.Count
End Sub
Count Rows with Data using VBA
You can also count rows where you have data by ignoring the blank rows.
The following code will take used range as the range to loop up at and loop through each row one by one and check if there’s a non-empty cell there, and if it is there it will consider it as a row with data, and in the end, show a message box with the total count of rows.
Sub vba_count_rows_with_data()
Dim counter As Long
Dim iRange As Range
With ActiveSheet.UsedRange
'loop through each row from the used range
For Each iRange In .Rows
'check if the row contains a cell with a value
If Application.CountA(iRange) > 0 Then
'counts the number of rows non-empty Cells
counter = counter + 1
End If
Next
End With
MsgBox "Number of used rows is " & counter
End Sub
There’s More
- Excel VBA Font (Color, Size, Type, and Bold)
- Excel VBA Hide and Unhide a Column or a Row
- How to Apply Borders on a Cell using VBA in Excel
- How to Find Last Row, Column, and Cell using VBA in Excel
- How to Insert a Row using VBA in Excel
- How to Merge Cells in Excel using a VBA Code
- How to Select a Range/Cell using VBA in Excel
- How to SELECT ALL the Cells in a Worksheet using a VBA Code
- How to use a Range or a Cell as a Variable in VBA
- How to use ActiveCell in VBA in Excel
- How to use Special Cells Method in VBA in Excel
- How to use UsedRange Property in VBA in Excel
- Loop Through a Range using VBA (Columns, Row, and UsedRange)
- VBA AutoFit (Rows, Column, or the Entire Worksheet)
- VBA Check IF a Cell is Empty + Multiple Cells
- VBA Enter Value in a Cell (Set, Get and Change)
- VBA Insert Column (Single and Multiple)
- VBA Named Range | (Static + from Selection + Dynamic)
- VBA Range Offset
- VBA Sort Range | (Descending, Multiple Columns, Sort Orientation
- VBA Wrap Text (Cell, Range, and Entire Worksheet)
This is VBA tutorial is written by Puneet Gogia for Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, and Excel for Mac. To learn more on VBA check out (What is VBA in Excel and Range and Cells Property in VBA).