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.


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.

Count All Rows in Range with Data from the Activesheet

  • 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.

Count All Rows in Range with Data from the Activesheet

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.

Using VBA to Count Range with Continuous Data in Excel

  • 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.

Using VBA to Count Range with Continuous Data in Excel

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

Using VBA to Count Range with Continuous Data in Excel

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.

Checking Only One Column to Count Rows Using VBA in Excel

  • 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.

Checking Only One Column to Count Rows Using VBA in Excel

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.

Checking Only One Column to Count Rows Using VBA in Excel

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.

Count Rows by Checking All Columns in Excel Using VBA

  • 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.

Count Rows by Checking All Columns in Excel Using VBA

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.

Count Rows by Checking All Columns in Excel Using VBA

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.

Employing VBA to Count Rows with Specific Word in Excel

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.

Employing VBA to Count Rows with Specific Word in Excel

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.


Related Articles

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo