# Excel VBA: Count Rows with Specific Data (8 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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

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

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

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems. Alok Paul

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  