Count Rows using VBA in Excel (Examples)

It’s pretty easy to count rows in Excel using VBA.

All you need to do is specify the range for which you want to count the rows and then use the Range.Count property to give you the value of the row count.

In this article, I will show you how to count rows using VBA in Excel in different scenarios.

Table of Contents

Count Rows in Specified Range

Below is the VBA code where I’ve specified the range in which I want to count the number of rows (which is A1:A10).

When you run this code, this is going to show you a message box with a count of the number of rows.

Sub CountRowsInSpecifiedRange()
    ' Declare a Range object variable
    Dim myRange As Range
    
    ' Set the Range object to the desired range
    Set myRange = Worksheets("Sheet1").Range("A1:A10")
    
    ' Declare a variable to store the row count
    Dim rowCount As Long
    
    ' Count the rows in the range
    rowCount = myRange.Rows.Count
    
    ' Display the row count in a message box
    MsgBox "The number of rows in the specified range is: " & rowCount
End Sub

The above code uses a Range object variable to specify the range of cells you want to count the rows for, which is A1:A10 in this example.

It counts the rows in that range using the Rows.Count method and stores the result in a variable named rowCount.

Finally, it displays a message box showing the number of rows.

The code is straightforward and should be easy to follow for beginners. You can modify the myRange variable to specify a different range for which you want to count the rows.

Also read: VBA to Delete Rows Based On Cell Value

Count Rows in Used Range

Below is the VBA macro code that would count the total number of rows in the used range in your worksheet.

Sub CountRowsInUsedRange()
    ' Declare a Worksheet object variable
    Dim ws As Worksheet
    
    ' Set the Worksheet object to the desired worksheet
    Set ws = Worksheets("Sheet1")
    
    ' Declare a variable to store the row count
    Dim rowCount As Long
    
    ' Count the rows in the used range of the worksheet
    rowCount = ws.UsedRange.Rows.Count
    
    ' Display the row count in a message box
    MsgBox "The number of rows in the used range is: " & rowCount
End Sub

The above code initializes a Worksheet object variable ws that represents the worksheet we’re interested in, which in this example is “Sheet1”.

Then, the UsedRange property of that worksheet is used to find all the used cells.

We count the rows in this used range using the Rows.Count method, and the result is stored in the variable rowCount.

Finally, a message box displays the number of rows in the used range.

The UsedRange property automatically adjusts to the portion of the worksheet that has data, making it a convenient and dynamic way to count the rows that are actually being used.

Also read: Delete Blank Rows Using VBA

Count Rows in Selection

If you only want to count the total number of rows in the selection that you have already made in the worksheet, you can use the vb code below:

Sub CountRowsInSelection()
    ' Declare a variable to store the row count
    Dim rowCount As Long
    
    ' Count the rows in the current selection
    rowCount = Selection.Rows.Count
    
    ' Display the row count in a message box
    MsgBox "The number of rows in the selected range is: " & rowCount
End Sub

In this code, we have used the built-in Selection object, which represents the currently selected range on the active worksheet.

We count the number of rows in this selection using the Rows.Count method.

The result is stored in a variable named rowCount. Finally, a message box pops up to display the counted number of rows.

The Selection object dynamically reflects whatever range of cells you’ve selected in Excel, making this code a versatile way to count rows in various selections without having to specify a fixed range.

Also read: VBA to Freeze Top Row in Excel

Count Non-Empty Rows

If you have a dataset where you have made a selection, and there are blank rows in the selection, you can use the code below to only count the nonblank rows.

Sub CountNonBlankRowsInSelection()
    ' Declare a variable to store the row count
    Dim rowCount As Long
    rowCount = 0
    
    ' Declare a Range object variable for each cell in the selection
    Dim cell As Range
    
    ' Loop through each row in the selection
    For Each cell In Selection.Rows
        ' Check if the row is non-blank
        If WorksheetFunction.CountA(cell.EntireRow) > 0 Then
            ' Increment the row count
            rowCount = rowCount + 1
        End If
    Next cell
    
    ' Display the row count in a message box
    MsgBox "The number of non-blank rows in the selected range is: " & rowCount
End Sub

The above code uses a For-Each loop to go through each cell in the selected rows.

Within the loop, the WorksheetFunction.CountA function checks if the entire row corresponding to the current cell is non-blank.

If it is, the row count is incremented by one.

Finally, a message box displays the total number of non-blank rows within the selection.

If any cell in a row contains data, the row is considered non-blank, and the row count increases. This code provides a way to count only the rows with data in your selection, skipping any blank rows.

Also read: VBA to Hide Rows Based On Cell Values

Count Rows Starting From Specific Cell

If you want to count the number of rows that have data in your worksheet but want to start from a specific row (say row 5), you can use the below code:

Sub CountRowsFromSpecificCell()
    ' Declare a Worksheet object variable
    Dim ws As Worksheet
    
    ' Set the Worksheet object to the desired worksheet
    Set ws = Worksheets("Sheet1")
    
    ' Declare variables to store the starting row and last row
    Dim startRow As Long, lastRow As Long
    
    ' Specify the starting row based on the specific cell (e.g., A5)
    startRow = Range("A5").Row
    
    ' Find the last used row in the same column (e.g., column A)
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Calculate the number of rows starting from the specific cell
    Dim rowCount As Long
    rowCount = lastRow - startRow + 1
    
    ' Display the row count in a message box
    MsgBox "The number of rows starting from the specific cell is: " & rowCount
End Sub

In the above code, we have set a Worksheet object variable ws to represent the worksheet we’re working with, which in this case is “Sheet1”.

We also define variables for the starting row and the last used row in the column. The starting row is determined based on the specific cell (here, “A5”). The last used row in the column is found using Cells().End(xlUp).Row.

We then calculate the row count by subtracting the starting row from the last row and adding 1 to include the starting row itself. Finally, a message box displays the row count.

This approach lets you dynamically count rows in a column, starting from a cell of your choice and continuing down to the last used cell in the same column.

Also read: Find Last Row Using VBA

Count Rows in an Excel Table

If you want to count the total number of rows in an Excel table, you can use the below VBA code.

Note: Excel tables are considered as list objects in VBA, so this code would be a little different than all the other codes that we have seen so far.

Sub CountRowsInTable()
    ' Declare a ListObject object variable to represent the Excel Table
    Dim myTable As ListObject
    
    ' Set the ListObject object to the desired table in the worksheet
    Set myTable = Worksheets("Sheet1").ListObjects("MyTable")
    
    ' Declare a variable to store the row count
    Dim rowCount As Long
    
    ' Count the rows in the table
    rowCount = myTable.ListRows.Count
    
    ' Display the row count in a message box
    MsgBox "The number of rows in the table is: " & rowCount
End Sub

The above code uses a ListObject object variable to specify the Excel Table you want to count the rows for, which is named “MyTable” in this example.

We then use the ListRows.Count property to count the number of rows in the Excel Table and store this count in a variable named rowCount.

Finally, a message box displays the total number of rows in the table.

The ListObject is Excel’s object representation for tables, and the ListRows.Count property specifically gives you the count of rows in the table.

So these are some of the simple and straightforward VBA codes that you can use when you want to count the number of rows in your Excel worksheet in different situations.

You can modify these VBA macro codes according to your needs.

Also read: Useful Excel VBA Macro Codes Examples

Other articles you may also like: