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 the 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
Related Tutorials
- Excel VBA Font (Color, Size, Type, and Bold)
- Excel VBA Hide and Unhide a Column or a Row
- Excel VBA Range – Working with Range and Cells
- Apply Borders on a Cell using VBA in Excel
- Find Last Row, Column, and Cell using VBA in Excel
- Insert a Row using VBA in Excel
- Merge Cells in Excel using a VBA Code
- Select a Range/Cell using VBA in Excel
- SELECT ALL the Cells in a Worksheet using VBA
- ActiveCell in VBA in Excel
- Special Cells Method in VBA in Excel
- UsedRange Property in VBA in Excel
- VBA AutoFit (Rows, Column, or the Entire Worksheet)
- VBA ClearContents (from a Cell, Range, or Entire Worksheet)
- VBA Copy Range to Another Sheet + Workbook
- 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)
- VBA Check IF a Cell is Empty + Multiple Cells