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.
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
- 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)