We use Excel VBA code to perform work by simply clicking a single button only. In this article, we will discuss how to count rows with specific data using Excel VBA with proper explanation.
Excel VBA Count Rows with Specific Data: 8 Examples
We will show 8Â different examples of Excel VBA to count rows with specific data. The following dataset will be used to apply sample operation.
1. Use VBA Rows.Count Property to Count Rows of a Specific Range
We will use the VBA Rows.Count property here to count the number of rows in Excel.
Step 1:
- Go to the Sheet panel of the Excel file. Press the right button of the mouse.
- Choose the View Code option from the list.
Now a window will appear to apply the VBA code. We will write any VBA code on here and run the code.
Step 2:
- Now, put the following VBA code on the command module.
Sub Count_Rows_Specific_Data_1()
Dim range_1 As Range
Dim Counter As Integer
Set range_1 = Range("B5:D9")
Counter = range_1.Rows.Count
MsgBox "Number of Rows are: " & Counter
End Sub
Step 3:
- Press F5 to run the code.
This VBA code counts the number of rows in the range B5:D9.
Read More:Â Excel VBA: Count Rows in Named Range
2. Excel VBA to Count Rows from User Selection
We will count the number of rows of the Name column based on the selection method. VBA code is based on the Rows.Count property.
Step 1:
- First, select all the cells of the Name column.
- Hit Alt+F11 to enter the command module.
- Copy and paste the following VBA code.
Sub Count_Rows_Specific_Data_2()
Dim range_1 As Range
Dim Counter As Integer
Set range_1 = Selection
Counter = range_1.Rows.Count
MsgBox "Number of Rows are: " & Counter
End Sub
Step 2:
- Press the F5 button to run the code.
We selected 5 rows, and the result is also the same.
Read More: How to Count Rows in Selection Using VBA in Excel
3. Excel VBA to Count Rows Based on Criteria
We want to set criteria and count the number of rows based on those criteria from our selected rows. Our criteria are how many rows are below 27 of the Age column, which means how many employees are below age 27.
Step 1:
- Press Alt+F11 to enter the command module.
- Write the following VBA code on the module.
Sub Count_Rows_with_Criteria()
Dim Count As Integer
Count = 0
For i = 1 To Selection.Rows.Count
   If Selection.Cells(i, 1) < 40 Then
       Count = Count + 1
   End If
Next i
MsgBox Count
End Sub
Step 2:
- Press F5 to run the code.
4. Use End Property to Count Non-blank Rows in a Column
Here, we will use the VBA End Property. This End property will find the last rows of a column. We should subtract the selected row position from the total count.
Step 1:
- Hit Alt+F11 to open the command module.
- Write the following VBA code on the module.
Sub Count_Rows_Specific_Data_4()
Dim range_1 As Range
Dim Counter As Integer
Set range_1 = Range("D5")
Counter = range_1.End(xlDown).Row
MsgBox "Number of Rows are: " & (Counter - 4)
End Sub
Step 2:
- Now, run the code by pressing F5.
We used Counter-4 in the MsgBox command, as started from cell D5.
5. Combine Rows.Count Property and End Property in Excel VBA
We will combine the Rows.Count and End Property in Excel VBA to count rows with specific data.
Step 1:
- Enter the VBA command module by pressing ALt+F11.
- Copy the VBA code below and paste it into the module.
Sub Count_Rows_Specific_Data_5()
Dim range_1 As Range
Dim Counter As Integer
Set range_1 = Cells(Rows.Count, 2)
Counter = range_1.End(xlUp).Row
MsgBox "Number of Rows are: " & (Counter - 4)
End Sub
Step 2:
- Press F5 and run the code.
We wanted to count the rows of the Name column, so used two (2) in the Cell command argument.
6. VBA IsEmpty Property to Count Rows without Data
We will use the VBA IsEmpty property to get the number of rows without data in Excel.
Step 1:
- Enter the VBA command module by pressing the Alt+F11Â keys.
- Put the following VBA code on the module.
Sub Count_Rows_Specific_Data_6()
Dim n As Long
Dim m As Long
Dim range_1 As Range
Dim cell_1 As Range
Set range_1 = Application.InputBox(Title:="Select a range", Prompt:="Range", Type:=8)
For Each cell_1 In range_1
   m = m + 1
   If IsEmpty(cell_1) Then
       n = n + 1
   End If
Next cell_1
MsgBox "Number of Blank Rows are: " & n
End Sub
Step 2:
- Run the code by pressing F5.
- A new dialog box will appear to input the range. We selected range B5:B9.
Step 3:
- Now, press OK.
We get the number of blank rows in the result.
7. Calculate Non-blank Rows with Data Using VBA
We want to count the non-blank rows of the Salary column. Follow the steps below.
Step 1:
- Press Alt+F11 to enter the command module.
- Copy the following VBA code and paste it into the module.
Sub Count_Rows_Specific_Data_7()
Dim n As Long
Dim m, range_1 As Range
Set range_1 = Range("D5:D9")
With range_1
For Each m In .Rows
If Application.CountA(m) > 0 Then
n = n + 1
End If
Next
End With
MsgBox "Number of used rows is " & n
End Sub
Step 2:
- Hit the F5 button to run the code.
We get only the non-blank rows here.
Read More: Excel VBA to Count Rows with Data
8. Count Rows Based on Specific Data Using Countif Property
We want to know the number of rows containing specific data. We will use the VBA Countif property for this.
Step 1:
- Press Alt+F11 to enter the command module.
- Copy and paste the VBA code below.
Sub Count_Rows_Specific_Data_8()
Dim n As Long
Dim m, range_1 As Range
Set range_1 = Range("B5:B9")
With range_1
For Each m In .Rows
If Application.CountIf(m, "John") > 0 Then
n = n + 1
End If
Next
End With
MsgBox "Number of used rows is " & n
End Sub
Step 2:
- Hit F5 to run the code.
We can see the result is 2 as John is present in two rows.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
In this article, we showed 8 examples of Excel VBA to count rows with specific data. We also added code to show blank rows in the article. I hope this will satisfy your needs. Please give your suggestions in the comment box.
