How to Count Rows with VBA in Excel (5 Approaches)

Get FREE Advanced Excel Exercises with Solutions!

Today I’ll show you how can count rows with VBA in Excel from any data set. I’ll show you how can count rows from a specific range, from a selected range, by matching a specific criterion, by matching a specific text value, and by excluding the blank cells.

Table of Contents hide

Download Practice Workbook

5 Methods to Count Rows with VBA in Excel

1. Use VBA Code to Count Rows of a Specific Range

2. Run Excel VBA Code to Count Rows of a Selected Range

3. Insert VBA Code to Count Rows with Criteria in Excel

4. Embed VBA Code to Count Rows Having a Specific Text Value

5. Count Rows with Blank Cells Using VBA in Excel

Conclusion

Download Practice Workbook

Count Rows with VBA.xlsm

5 Methods to Count Rows with VBA in Excel

Here we’ve got a data set with the Names of some students and their Marks in English at a school called Sunflower Kindergarten.

Today our objective is to count the total number of rows using a VBA code.

1. Use VBA Code to Count Rows of a Specific Range

⧪ Step 1:

➤ Press ALT+F11 on your keyboard. The VBA window will open.

⧪ Step 2:

➤ Go to the Insert tab in the VBA window.

➤ From the options available, select Module.

⧪ Step 3:

➤ A new module window called “Module 1” will open.

➤ Insert the following VBA code in the module.

Code:

Sub Count_Rows()
Dim rng As Range
Set rng = Range("B4:C13")
MsgBox rng.Rows.Count
End Sub

Notes:

This code produces a Macro called Count_Rows.
The 3rd line of the code contains the specified range “B4:C13″. I want to count the number of rows in this range.
You use your one.

⧪ Step 4:

➤ Save the workbook as Excel Macro-Enabled Workbook.

⧪ Step 5:

➤ Return to your worksheet and press ALT+F8 on your keyboard.

➤ A dialogue box called Macro will open. Select Count_Rows (The name of the Macro) and click on Run.

⧪ Step 6:

➤ You will find a small message box showing the number of total rows (10 in this case).

➤ Click OK to exit.

2. Run Excel VBA Code to Count Rows of a Selected Range

In the previous method, we counted the number of rows of a specific range (B4:C13).

But we can also use a VBA code to count the number of rows in any selected range according to our wish.

The steps are all same as Method 1 (Step 1-6).

⧪ Just in Step 3, instead of the previous code, insert this code:

Code:

Sub Count_Selected_Rows()
Dim rng As Range
Set rng = Selection
MsgBox rng.Rows.Count
End Sub

Note:

This code creates a module called Count_Selected_Rows.

⧪ And in Step 5, before running the code, select a range first. Here I have selected my whole data set (Without the Column Headers).

⧪ Then press ALT+F8, select Count_Selected_Rows, and click on Run.

You will get a message box showing you the total number of rows in your selected range (10 in this case.)

3. Insert VBA Code to Count Rows with Criteria in Excel

We can also use a VBA code to count the total number of rows that maintain a specific criterion.

For example, let’s create a Macro that will count the number of students who got marks less than 40.

The steps are also all same as Method 1 (Step 1-6).

⧪ Just in Step 3, change the VBA code to this:

Code:

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

Note:

This code creates a module called Count_Rows_with_Criteria.
In line 6, we used “<40” because this is the criterion we are using. You change it according to your need.

⧪ And in Step 5, before running the code, select the range of cells with the criteria. Here I have selected only column C (C4:C13) because the criterion lies there.

⧪ Then press ALT+F8, select Count_Rows_with_Criteria, and click on Run.

You will get a message box showing you the total number of rows that fulfill your criterion (3 in this case.)

4. Embed VBA Code to Count Rows Having a Specific Text Value

You can also use a VBA code to count the number of rows that contain a specific text value.

Look at this new data set.

We have the Book Records of some books of a bookshop called Martin Bookstore.

Let’s create a Macro that will count the number of books having a specific text from this data set.

The steps are also all same as Method 1 (Step 1-6).

⧪ Just in Step 3, change the VBA code to this:

Code:

Sub Count_Rows_with_Specific_Text()
Dim Count As Integer
Count = 0
Dim Text As String
Text = InputBox("Enter the Text Value: ")
LText = LCase(Text)
For i = 1 To Selection.Rows.Count
    Words = Split(Selection.Cells(i, 1))
    For Each j In Words
        LWord = LCase(j)
        If LText = LWord Then
            Count = Count + 1
        End If
    Next j
Next i
MsgBox Count
End Sub

Note:

This code creates a module called Count_Rows_with_Specific_Text.

⧪ And in Step 5, before running the code, select the range of cells with the text values. Here I have selected range B4:B13 (Name of the Books).

⧪ Then press ALT+F8, select Count_Rows_with_Specific_Text, and click on Run.

⧪ An Input Box will appear that will ask you to enter the specific text value that you want to match.

For the sake of this example, I have entered it as “history”.

Finally, you will get a message box showing you the total number of rows that contain the specific text (3 in this case.)

5. Count Rows with Blank Cells Using VBA in Excel

Finally, we will develop a Macro that will count the total number of rows excluding the blank cells from a data set.

Look at this new data set.

We have the Marks of some candidates in a recruitment test of a company called APEX group.

But unfortunately, some candidates could not appear at the test and there are blank cells in place of their marks.

Let’s develop a Macro that will count the total number of rows excluding the blank cells.

That means, how many candidates did appear in the test.

The steps are all same as Method 1 (Step 1-6).

⧪ Just in Step 3, enter this VBA code in place of the earlier one:

Code:

Sub Count_Rows_with_Blank_Cells()
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

Note:

This code creates a module called Count_Rows_with_Blank_Cells.

⧪ In Step 5, before running the code, select the range of cells with the blank cells. Here I have selected range C4:C13 (Marks in the Test).

⧪ Then press ALT+F8, select Count_Rows_with_Blank_Cells, and click on Run.

You will get a message box showing you the total number of rows excluding the blank cells (7 in this case.)

Conclusion

Using these methods, you can count rows with VBA from a data set in Excel matching various conditions. Do you have any problems? Feel free to ask us.