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

### 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

## 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

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

Advanced Excel Exercises with Solutions PDF