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

Get FREE Advanced Excel Exercises with Solutions!

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 count rows in named range


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.

excel vba count rows in named range


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

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

  • 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

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

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

excel vba count rows in named range

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

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

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

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

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

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


Similar Readings:


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

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

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

Run → Run Sub/UserForm

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

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.

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

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

Count the Last Row Using a VBA Code in Excel

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

Count the Last Row Using a VBA Code in Excel

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

Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo