Count Rows using VBA in Excel

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

1. First, you need to define the range for which you want to count the rows.
2. After that, use a dot (.) to open the list of properties and methods.
3. Next, type or select the “Rows” property.
4. 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