Md. Abdur Rahim Rasel
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
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
4 Suitable Ways to Count Rows in Named Range Using VBA in Excel
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 Rows with Data in Excel (4 Formulas)
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.
Related Content: Excel VBA to Count Rows with Data (4 Examples)
Similar Readings:
- How to Count Rows in Selection Using VBA in Excel
- Excel VBA: Count Rows in a Sheet (5 Examples)
- Count Rows in Group with Pivot Table in Excel (Step-by-Step Guideline)
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 Formula in Excel (5 Quick Methods)
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 Filtered Rows in Excel with VBA (Step-by-Step Guideline)
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.
