When we use VBA to automate Excel operations on data or tables, one of the most common things we want to do is to find the number of rows of data or the number of records, in a worksheet.

In this article, we’ll go through a few key approaches to count the number of rows of data in Excel ranges.

Count Rows of Data in a Range

The Range contains continuous data

In this example, we have a list of monthly expense data as shown in the picture below. The list in continuous with no “broken rows” in between (i.e. no rows within the range are blank). It is easy to find the number of rows in a list like this.

The macro “countDataRows1” below uses range.Rows.Count to find the number of rows of data in the current selection. To use the macro, we first select the list of data, and run the macro. The macro also returns the answer in a message box.

Sub countDataRows1()
Dim tmp As Long
tmp = Selection.Rows.Count
MsgBox tmp & " rows of data in the selection"
End Sub

However, you may find it inconvenient to have to manually select the entire list before running the macro. We can enhance the macro to have it automatically find the “CurrentRegion” of the table range. (See line 3 of macro “countDataRows2” below) You’ll only have to select any cell within the list before running the macro.

Sub countDataRows2()
Dim tmp As Long
tmp = Selection.CurrentRegion.Rows.Count
MsgBox tmp & " rows of data in the selection"
End Sub

Some rows in the range are empty

Unlike databases tables (such as Access), Excel tables are not always continuous. We often work in Excel worksheets with “free-style” layouts and our tables were designed with breaks (empty rows) to make them easier to read. However, this causes an additional challenge when we try to find the actual number of populated rows in a worksheet with VBA.

Let’s say we have an income statement as shown below. There are some empty rows (e.g. rows 6, 25, and 28) which separate the key sections of the financial statement. We want to use VBA to find:

1. The total number of rows of the income statement (i.e. the height of the statement)
2. The number of rows that are used/filled with data

Some bad news here is that the method of “CurrentRegion” we used in the previous example (macro “countDataRows2”) won’t work anymore in this case. To illustrate the issue, if we select cell A2 manually, and then press the keyboard shortcut Ctrl+Shift+8 to select the CurrentRegion, only the first block of data from rows 1 to 5 will be selected (because row 6 is empty). Therefore we need an alternative VBA approach to find the entire range of our income statement.

We can use the “UsedRange” property of the ActiveSheet object to find the range of the income statement without being affected by the empty rows, then count the number of rows. The macro “countDataRows3” below does the job in one step in line 3.

Sub countDataRows3()
Dim tmp As Long
tmp = ActiveSheet.UsedRange.Rows.Count
MsgBox "Total number of rows of the income statement is " & tmp
End Sub

The next step is to find the number of used rows with data in this income statement. I’m going to demonstrate two approaches below.

Approach 1 – only check one column to determine used rows

Assuming that the cells in Column are always populated for used rows, we can use the COUNTA() worksheet function to count the number of used cells in column A of the UsedRange of the worksheet.

Sub countDataRows4()
Dim tmp As Long
Dim tmp2 As Long
With ActiveSheet.UsedRange
    'number of rows of range
    tmp = .Rows.Count
    'number of used rows with data
    tmp2 = Application.CountA(.Columns(1))
End With
MsgBox "Total number of rows of the income statement is " & tmp & Chr(10) & "Number of used rows is " & tmp2
End Sub

In the above macro “countDataRows4”, in line 8, the Excel built-in worksheet function CountA has been called in VBA. It is very important to note how it has been called.  You can always call an Excel built-in worksheet function using the syntax: “Application.<function_name>” (and then the input parameters).

After running the macro, a message box returns both answers for total number of rows and the number of used rows. (The message box shown below is from the Mac version of Excel)

Approach 2 – check all columns to determine used rows

However, in the event we have to check through all columns to ensure the entire row is empty, we can use a For-Next loop together with the COUNTA worksheet function

The idea is to (1) find the UsedRange, (2) loop through every row of the UsedRange, and (3) use the COUNTA worksheet function to check whether any one of the cells in each row is non-empty.

Sub countDataRowswithBreaks()
Dim counter As Long
Dim r As Range
With ActiveSheet.UsedRange
    'loop through every row in the usedrange
    For Each r In .Rows
        'check if a row contain any used cell
        If Application.CountA(r) > 0 Then
            'counts the number of rows with used cells
            counter = counter + 1
        End If
    Next
End With
MsgBox "Number of used rows is " & counter
End Sub

If you’re interested in techniques to delete empty rows in Word instead of Excel, have a look at this article.

Count Rows with Specific Word

How about if we want to count the number of rows which contain the word “Expense”? We can adapt the previous macro and use the “CountIf” worksheet function (instead of “CountA”).

In the macro “countDataRowswithWord” below, line 7, the CountIf function is used to count the number of cells in each row which contains the word “Expense”. We use the wildcard “*” here, so that the function counts any cell with part of the content which contains “Expense”, for example, “Other Expense 1”, “Total Expenses”.

Sub countDataRowswithWord()
Dim counter As Long
Dim r As Range
With ActiveSheet.UsedRange
    'loop through every row in the usedrange
    For Each r In .Rows
        'check if a row has any cell with partial match
        If Application.CountIf(r, "*Expense*") > 0 Then
		'counts the number of rows which match criteria
            counter = counter + 1
        End If
    Next
End With
MsgBox "Number of rows is with content match: " & counter
End Sub

Conclusion

We have gone fully through some key techniques to find the number of rows in a range. In addition to Range.Rows.Count, you can use “CurrentRegion” or “UsedRange” to quickly find out the area of a block of data. Also, you can use a For-Next loop to go through every row in a range, together with Excel built-in functions such as “COUNTA()” or “COUNTIF()” to perform more detailed check of specific criteria.