There are many ways to count rows in a range with data but you can do it quickly and smartly using VBA Macros. Especially for a large dataset, it’s more feasible. From this article, you will learn 5 quick macros to count rows in range with data using Excel VBA with sharp steps and clear illustrations.
Download Practice Workbook
You can download the free Excel template from here and practice on your own.
5 Macros to Count Rows in Range with Data in Excel
To explore the macros, we’ll use the following dataset that represents some salespersons’ sales in two consecutive years.
1. Count All Rows in Range with Data from the Activesheet
Have a look that I have deleted the data from two rows. Now we’ll use VBA to count the used rows from the whole sheet.
Steps:
- Right-click your mouse on the sheet title.
- Select View Code from the Context menu.
Soon after, a VBA window will open up.
- Type the following codes in it-
Sub Count_AllRows()
Dim Counter_n As Long
Dim xRng As Range
With ActiveSheet.UsedRange
For Each xRng In .Rows
If Application.CountA(xRng) > 0 Then
Counter_n = Counter_n + 1
End If
Next
End With
MsgBox " In the Dataset Count of Used Rows are " & Counter_n
End Sub
- Later, just click the Run icon to run the codes.
Code Breakdown:
- Here, I created a Sub Procedure, Count_AllRows where I declared two variables Counter_n as Long and xRng as Range.
- Next, I used UsedRange to select a range with data from the sheet then used a For loop to go through the Entire Selection.
- Within the For loop I used an IF statement to check the non-empty cells if it is greater than 0 then it will increment the Counter_n.
- Finally, used a MsgBox to show the count of all selected rows.
- Now, Run the code.
Then you will get all the used rows including the rows that contain headers and titles in a Message Box.
Read More: Excel VBA to Loop through Range until Empty Cell (4 Examples)
2. Using VBA to Count Range with Continuous Data in Excel
By using this VBA macro you will be able to count the used rows from a continuous data range.
Steps:
- Select a continuous range. I selected B5:D7.
- After that right-click your mouse on the sheet title.
- And then select View Code from the Context menu.
- Later, write the following codes in the VBA
Sub Continous_Data()
Dim CD As Long
CD = Selection.Rows.Count
MsgBox CD & " rows are counted in the selection"
End Sub
- Then click the Run icon to run the codes.
Code Breakdown:
- Here, I created a Sub Procedure, Continous_Data, and declared a variable CD as Long.
- Then I used Rows.Count to count the rows from the selection.
- Finally, used a MsgBox to show the counted row numbers.
- Save the code and go back to the worksheet.
- Select any range. Here, I selected the range B5:D7.
- Open the Developer tab >> select Macros
A dialog box of Macros will pop up.
- Finally, select the Macro Name as specified Continous_Data and click Run.
Soon after, a Message Box will notify you of the used rows in the continuous range.
Similar Readings
- Excel Macro: Sort Multiple Columns with Dynamic Range (4 Methods)
- VBA to Loop through Rows and Columns in a Range in Excel (5 Examples)
- How to Convert Range to Array in Excel VBA (3 Ways)
- How to Use VBA to Select Range from Active Cell in Excel (3 Methods)
3. Checking Only One Column to Count Rows Using VBA in Excel
Here, we’ll count used rows by checking only one column from the used data range. For that, I have removed some data from the dataset and now we’ll count the used rows based on column number 3 relative to the used data range.
Steps:
- Open the VBA window by selecting View Code from the Context menu after right-clicking on the sheet title.
- Then write the following codes–
Sub CountRows_OneColumn()
Dim Rw As Long
With ActiveSheet.UsedRange
Rw = Application.CountA(.Columns(3))
End With
MsgBox "Total used rows: " & Rw
End Sub
- To run the codes, click on the Run icon.
Code Breakdown:
- I created a Sub Procedure, CountRows_OneColumn, and declared a variable Rw as Long.
- Then used UsedRange to select the rows from the used range in the ActiveSheet.
- Next, I used CountA(.Columns(3)) to count the filled rows of column 3 from the used range.
- Finally, used MsgBox to show the output.
- Follow the steps explained in section 2 to bring the Macros dialog box.
- After that select the specified Macro Name.
Soon after, Excel will show the output in a pop-up Message Box.
Read More: Excel VBA to Select Used Range in Column (8 Examples)
4. Count Rows by Checking All Columns in Excel Using VBA
In this section, we’ll count only those rows that are used at least in one column. I have modified the dataset for that.
Steps:
- Open the VBA window as like the previous method.
- Then type the following codes in that window-
Sub CountRows_AllColumns()
Dim Counter_n As Long
Dim Rng As Range
With ActiveSheet.UsedRange
For Each Rng In .Rows
If Application.CountA(Rng) & amgt0 Then
Counter_n = Counter_n + 1
End If
Next
End With
MsgBox "Total used rows: " & Counter_n
End Sub
- Click the Run icon to run the codes.
Code Breakdown:
- First, I created a Sub Procedure, CountRows_AllColumns, and declared two-variable- Counter_n As Long and Rng As Range.
- Then used UsedRange to select the used range from the ActiveSheet.
- After that, I used a For Loop and while checking CountA(Rng) to count the range of rows using the IF statement. If it is TRUE then Counter_n = Counter_n + 1 will continue the counting.
- You can follow the steps explained in section 2 to bring the Macros dialog box(anchor).
- Select the Macro Name as determined in the codes.
Then you will get the number of total used rows by checking all columns.
Read More: VBA to Loop Through Rows in Range in Excel (6 Examples)
5. Employing VBA to Count Rows with Specific Word in Excel
You can use VBA to count the used rows with specific words too. I have added a new column in the dataset which represents the selling regions. Let’s count the rows which contain the word ‘UK’.
Steps:
- Open the VBA window by selecting View Code from the Context menu after right-clicking on the sheet title.
Write the following codes in it-
Sub CountRows_Word()
Dim Cntr As Long
Dim xRng As Range
With ActiveSheet.UsedRange
For Each xRng In .Rows
If Application.CountIf(xRng, "*UK*") & amgt0 Then
Cntr = Cntr + 1
End If
Next
End With
MsgBox "Number of rows with the word: " & Cntr
End Sub
- After that click the Run icon.
Code Breakdown:
- First, I created a Sub Procedure, CountRows_Word, and then declared two variables- Cntr As Long and xRng As Range.
- Then used UsedRange to select the used range from the ActiveSheet.
- Later, used a For Loop to go through the entire selection.
- Next, I used the IF statement to search for the word ‘UK’ and to count it. If the condition is TRUE or the UK word is found then Cntr = Cntr + 1 will continue the counting.
- Finally, MsgBox will show the output.
- By following the steps explained in section 2 you can bring the Macros dialog box(anchor).
- Select the Macro Name.
Soon after, you will get the output like the image below.
Read More: Excel VBA: Loop Through Columns in Range (5 Examples)
Conclusion
I hope the procedures described above will be good enough to count rows in range with data using Excel VBA. Feel free to ask any question in the comment section and please give me feedback.