Excel VBA: Counting the Number of Rows within Data (Guide)
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.
Contents
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:
- The total number of rows of the income statement (i.e. the height of the statement)
- 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) &amp;gt; 0 Then 'counts the number of rows with used cells counter = counter + 1 End If Next End With MsgBox "Number of used rows is " &amp;amp; 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*") &amp;gt; 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: " &amp;amp; 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.