How to Use VBA to Count Rows in Range with Data in Excel (5 Macros)

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.

Table of Contents hide

Download Practice Workbook

5 Macros to Count Rows in Range with Data in Excel

1. Count All Rows in Range with Data from the Activesheet

2. Using VBA to Count Range with Continuous Data in Excel

3. Checking Only One Column to Count Rows Using VBA in Excel

4. Count Rows by Checking All Columns in Excel Using VBA

5. Employing VBA to Count Rows with Specific Word in Excel

Conclusion

Download Practice Workbook

You can download the free Excel template from here and practice on your own.

VBA to Count Rows in Range with Data.xlsm

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.

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

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.

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.

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.

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.