Counting the total number of rows in a sheet is quite easy for a smaller size of dataset. However, if the size of the data table becomes large, then it’s quite difficult to manually count all the rows in a sheet in Excel. But if you use the VBA script, you can easily get the count of the rows in a sheet. In this article, you will learn 5 different methods to count rows in a sheet using VBA in Excel.
Excel VBA to Count Rows in a Sheet: 5 Examples
I will use the following dataset throughout the entire article to demonstrate all the methods to count rows in a sheet using VBA in Excel.
1. Manually Select a Range to Count Rows in a Sheet Using VBA in Excel
If you want to manually select a range and then count rows in a sheet, then follow the steps below:
❶ Press ALT + F11 to open the VBA editor.
❷ Go to Insert > Module.
❸ Copy the following code:
Sub SelectRowsToCount()
Dim a As Range
Set a = Selection
MsgBox a.Rows.Count
End Sub❹ Paste and save the code in the VBA editor.
❺ After that press ALT + F8 keys to avail the Macro dialog box.
❻ Select the function SelectRowsToCount and hit the Run button.
After that, a little dialog box will appear and show the row number in your selection area just like the picture below:
Read More: How to Count Rows in Selection Using VBA in Excel
2. Insert a Range to Count Rows in a Sheet Using VBA in Excel
If you are looking for VBA codes to input a range and count the number of rows in the given range, then follow the steps below:
❶ First of all, open the VBA editor by pressing ALT + F11 keys.
❷ Then create a new Module from the Insert tab.
❸ Now copy the code below:
Sub InputRangeToCountRows()
Dim a As Range
Set a = Range("B5:D12")
MsgBox a.Rows.Count
End Sub❹ Take the code in the VBA editor and save it.
❺ Now press the ALT + F8 keys to get the Macro window.
❻ From the Macro name list select InputRangeToCountRows and hit the Run button.
Now you will get a small dialog box with the total number of rows in your input range as in the screenshot below:
Read More: Excel VBA: Count Rows in Named Range
3. Add a Criteria to Count Rows in a Sheet Using VBA in Excel
In this method, I will show you how to count rows using VBA in Excel based on criteria.
We will count all the rows having “AHF 500”. To do that follow the steps below:
❶ Open the VBA editor by pressing ALT + F8 keys.
❷ Go to Insert > Module.
❸ Copy the code below:
Sub CriteriaBasedRowCount()
Dim Count As Integer
Count = 0
For i = 1 To Selection.Rows.Count
If Selection.Cells(i, 1) = "AHF 500" Then
Count = Count + 1
End If
Next i
MsgBox Count
End Sub❹ Paste and save the code in the VBA editor.
❺ Press ALT + F8 to get the Macro dialog box.
❻ From the Macro name list choose CriteriaBasedRowCount and hit Run.
After that, you will get the number of rows having “AHF 500” in them.
4. Input Text Values to Count Rows in a Sheet Using VBA in Excel
Using this method, you will get a pop-up dialog box to input text values. Based on the input data, the VBA script will return the number of rows that contain the input text.
Here are the steps to follow:
❶ Press ALT + F11 to open the VBA editor.
❷ Create a new Module from the Insert tab.
❸ Copy the following code:
Sub InputValuesToCountRows()
Dim Count As Integer
Count = 0
Dim mmm As String
mmm = InputBox("Input here: ")
Lmmm = LCase(mmm)
For i = 1 To Selection.Rows.Count
Words = Split(Selection.Cells(i, 1))
For Each j In Words
LWord = LCase(j)
If Lmmm = LWord Then
Count = Count + 1
End If
Next j
Next i
MsgBox Count
End Sub❹ Paste and save the code in the VBA editor.
❺ Press ALT + F8 to open the Macro dialog box.
❻ From the Macro name choose InputValuesToCountRows and click on the Run button.
❼ Insert text values in the pop-up dialog box and hit OK.
Finally, you will get the row number count in a small dialog box as in the picture below:
Read More: Excel VBA to Count Rows with Data
5. Count Rows Excluding the Blanks in a Sheet Using VBA in Excel
Use this method to count all the rows in a sheet excluding the blank rows.
❶ First, press ALT + F8 to open the VBA editor.
❷ Then go to Insert > Module.
❸ Now copy the following code:
Sub CountRowsExcludingBlanks()
Dim Count As Integer
Count = 0
For i = 1 To Selection.Rows.Count
If Selection.Cells(i, 1) <> "" Then
Count = Count + 1
End If
Next i
MsgBox Count
End Sub❹ Paste and save the above code in the VBA editor.
❺ Press ALT + F8 to open the Macro dialog box.
❻ From the Macro name section choose CountRowsExcludingBlanks and hit the Run button.
After that, you will get the count of the total number of rows in a sheet excluding all the blank rows just like the screenshot below:
Things to Remember
- Press ALT + F11 to open the VBA editor.
- To open the Macro dialog box, press ALT + F8.
Download Practice Workbook
You can download the Excel file from the following link and practice along with it.
Conclusion
To sum up, we have discussed 5 ways to count rows in a sheet using VBA in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries ASAP.
