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

In this article we’ll cover how to use VBA to 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.

Suppose we have a data set with the Names of some students and their marks in English.

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

Method 1 – Counting Rows of a Specific Range

Steps:

Press ALT+F11 on your keyboard to open the VBA window.

Go to the Insert tab in the VBA window.
Select Module.

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. We want to count the number of rows in this range.

Save the workbook as type Excel Macro-Enabled Workbook.

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

A dialog box called Macro will open.

Select Count_Rows (The name of the Macro) and click on Run.

A small message box showing the number of total rows (10 in this case) appears.

Click OK to exit.

Method 2 – Counting Rows of a Selected Range

We can also use a VBA code to count the number of rows in any selected range, as opposed to an entire range.

Steps:

The steps are all the same as in Method 1, except 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.

Before running the code, select a range first. Here, the whole data set without the Column Headers.

Press ALT+F8, select Count_Selected_Rows, and click on Run.

A message box appears showing the total number of rows in the selected range (10 in this case).

Method 3 – Counting Rows with Criteria

Let’s create a macro that will count the number of students who achieved marks less than 40.

Steps:

The steps are also all the same as Method 1, except 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.
Before running the code, select the range of cells with the criteria. Here, column C (C4:C13).

Press ALT+F8, select Count_Rows_with_Criteria, and click on Run.

A message box appears showing the total number of rows that fulfill the criterion (3 in this case).

Method 4 – Counting Rows Having a Specific Text Value

In this new data set, we have the Names and Prices of some books.

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

Steps:

The steps are also all the same as Method 1.
Just 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.

Before running the code, select the range of cells with the text values. Here, range B4:B13 (Name of the Book).

Press ALT+F8, select Count_Rows_with_Specific_Text, and click on Run.

An Input Box will appear asking to enter the specific text value to match.

Enter some text, for example “history“.

A message box appears showing the total number of rows that contain the specific text (3 in this case).

Method 5 – Counting Rows with Blank Cells

We can also count the total number of rows excluding those containing blank cells.

In this new data set, we have the Marks of some Candidates in a Recruitment Test.

Some candidates did not take the test so 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.

Steps:

The steps are all the same as Method 1.
Just enter this VBA code instead:

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: