Excel VBA: Count Rows in Named Range (4 Methods)

We can count rows in a named range by using workbook functions or conditional formatting but if we work with a large amount of data then using VBA (Visual Basic for Applications) is much more effective and time-saving. Today, in this article, we’ll learn four quick and suitable ways how to VBA count rows in named range in Excel effectively.

VBA to Count Rows in Named Range (Quick View)

Sub Count_Rows_Continuous()
Dim R As Long
R = Selection.Rows.Count
MsgBox R & " rows with data in the selection"
End Sub

Excel VBA to Count Rows in Named Range: 4 Suitable Ways

Let’s say, we have a dataset that contains information about several Students of the Cantonment School. The identification number of the students and their CGPA is given in columns C, and D respectively. We will count rows in a named range by applying a simple VBA Code. Here’s an overview of the dataset for today’s task.

1. Develop a VBA Code to Count Rows If the Range Contains Continuous Data in Excel

Now I’ll show how to count rows in named range by using a simple VBA code if you want to count them from a selection of continuous data. It’s very helpful for some particular moments. Let’s follow the instructions below to learn!

Step 1:

• First of all, select the continuous data rows that you want to select from your dataset, and then from your Developer tab, go to,

Developer → Visual Basic

• After clicking on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications – Count Rows will instantly appear in front of you. From that window, we will insert a module for applying our VBA code. To do that, go to,

Insert → Module

Step 2:

• Hence, the Count Rows module pops up. In the Count Rows module, write down the below VBA

Sub Count_Rows_Continuous()
Dim R As Long
R = Selection.Rows.Count
MsgBox R & " rows with data in the selection"
End Sub

• After that, run the VBA To do that, go to,

Run → Run Sub/UserForm

• After running the code, a message box named Microsoft Excel will appear instantly in front of you showing a message as 10 rows with data in the selection.

Read More: How to Count Filtered Rows in Excel with VBA

2. Use a VBA Code to Count Rows If the Range Contains Some Empty Data in Excel

We keep some rows empty for our convenience of work. From our dataset, we will count the total rows with empty data of the student of the Cantonment School. Let’s follow the steps below to learn!

Step 1:

• According to method 1, insert a new module and type the below VBA code to delete cells. The VBA code is,

Sub Count_Rows_with_Empty_Data()
Dim R As Long
R = ActiveSheet.UsedRange.Rows.Count
MsgBox "Total number of rows with empty data of the students are " & R
End Sub

• Hence, run the VBA To do that, go to,

Run → Run Sub/UserForm

Step 2:

• While running the code, a message box named Microsoft Excel will appear instantly in front of you showing a message as the Total number of rows with empty data of the students are 16.

Similar Readings:

• How to Count Rows in Selection Using VBA in Excel
• Excel VBA: Count Rows in a Sheet
• Excel VBA to Count Rows with Data

3. Apply the COUNTA Function in VBA Code to Count Rows in Excel

Now, we will apply the COUNTA function in a VBA code to count rows according to specific column data. The COUNTA function is the easiest and most time-saving way to count rows according to specific column data. Please follow the steps below to learn!

Step 1:

• Similarly, according to method 1, insert a new module and type the below VBA code to delete cells. The VBA code is,

Sub Count_Rows()
Dim R As Long
Dim M As Long
With ActiveSheet.UsedRange
    R = .Rows.Count
    M = Application.CountA(.Columns(1))
End With
MsgBox Worksheets("COUNTA").UsedRange.Rows.Count
MsgBox "Total used rows is " & M
End Sub

• After that, run the VBA To do that, go to,

Run → Run Sub/UserForm

Step 2:

• While running the code, a message box named Microsoft Excel will appear instantly in front of you showing a message as 14 which means we are using the total 14 rows as the used range of our dataset.

• Further, click on the OK option, instantly a new dialog box with a similar name as Microsoft Excel pops up showing a message as the Total used rows is 12.

Read More: How to Count Rows with Data in Column Using VBA in Excel

4. Count the Last Row Using a VBA Code in Excel

In this method, we’ll count the last row of our dataset by using a VBA code in Excel. We can easily do that. To do that, please follow the instructions below.

Step 1:

• According to method 1, insert a new module and type the below VBA code to delete cells. The VBA code is,

Sub Count_Last_Row()
    Dim x As Range, y As Long
    For Each x In Sheets("Last Row").Range("B2:D14").Rows
        If x.Row > y Then y = x.Row
    Next
    MsgBox "The last row of our active dataset is the row number " & y
End Sub

• Further, run the VBA To do that, go to,

Run → Run Sub/UserForm

Step 2:

• After running the VBA code, you will be able to count the last row of our dataset. The last row number of our dataset is 14 which has been given in the below screenshot.

Read More: How to Count Rows with VBA in Excel

Things to Remember

👉 You can pop up Microsoft Visual Basic for Applications window by pressing Alt + F11 simultaneously.

👉 If a Developer tab is not visible in your ribbon, you can make it visible. To do that, go to,

File → Option → Customize Ribbon

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

I hope all of the suitable methods mentioned above to count rows in a named range with VBA code will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.

Related Articles

• How to Count Visible Rows in Excel
• How to Use Excel to Count Rows with Value
• How to Count Rows with Text in Excel
• How to Count Rows with Multiple Criteria in Excel