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.

**Table of Contents**hide

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

**8 VBA Examples: Count Rows with Specific Data in Excel**

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 (4 Methods)**

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

**Read More:** **How to Count Rows with Multiple Criteria in Excel (6 Methods)**

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

**Read More:** **How Excel Count Rows with Value (8 Ways)**

**Similar Readings:**

**How to Count Rows with Data in Column Using VBA in Excel (9 Ways)****Excel VBA: Count Rows in a Sheet (5 Examples)****Count Rows in Group with Pivot Table in Excel (Step-by-Step Guideline)**

**5. ****Combine Rows.Count Property and End Property in Excel VBA**

We will combine the **Rows.Count** and **End Property** in this section.

**Step 1:**

- Enter the
**VBA**command module by pressing**ALt+F11**. - Copy the
**VBA**code below and paste it on 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.

**Related Content: How to Count Rows with VBA in Excel (5 Approaches)**

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

**Read More: How to Count Rows with Data in Excel (4 Formulas)**

**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 on 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 (4 Examples)**

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

**Related Content:** **Excel Count Visible Rows (Formula and VBA Code)**

**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 have a look at our website **Exceldemy.com** and give your suggestions in the comment box.