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

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.


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

Use VBA Rows.Count Property to Count Rows of a Specific Range

Now a window will appear to apply the VBA code. We will write any VBA code on here and run the code.

Use VBA Rows.Count Property to Count Rows of a Specific Range

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

Use VBA Rows.Count Property to Count Rows of a Specific Range

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

Excel VBA to Count Rows from User Selection

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

Excel VBA to Count Rows Based on Criteria

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

Use End Property to Count Rows with Data in a Column

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:


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

Combine Rows.Count Property and End Property in Excel VBA

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

Excel VBA IsEmpty Property to Count Rows without Data

Step 2:

  • Run the code by pressing F5.
  • A new dialog box will appear to input the range. We selected Range B5:B9.

Excel VBA IsEmpty Property to Count Rows without Data

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

Calculate Non-blank Rows with Data Using Excel VBA

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

Count Rows Based on Specific Data Using Countif Property

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.


Related Articles

Alok

Alok

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

Leave a reply

ExcelDemy
Logo