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

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.


Download Practice Workbook


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.

Data Set to Count Rows with VBA in Excel

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.

VBA Window to Count Rows with VBA in Excel

Step 2:

Go to the Insert tab in the VBA window. 

From the options available, select Module.

I

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.

VBA Code to Count Rows from Specific Range in Excel

Step 4:

Save the workbook as Excel Macro-Enabled Workbook.

Saving Workbook to Count Rows with VBA in Excel

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.

Running VBA Code to Count Rows in Excel

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.

VBA Code to Count Rows with VBA in Excel

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

Running Macros to Count Rows with VBA in Excel

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.

VBA Code to Count Rows with VBA in Excel

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

Running Macros to Count Rows with VBA in Excel

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

Output of Running Macros to Count Rows with VBA in Excel


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.

VBA Code to Count Rows with VBA in Excel

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

Selecting Data Set to Count Rows with VBA in Excel

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

Input Box to Count Rows with VBA

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

Output of Running Macros to Count Rows with VBA in Excel


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.

Data Set to Count Rows with VBA in Excel

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

Selecting Data Set to Count Rows with VBA in Excel

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

Running Macros to Count Rows with VBA in Excel

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.


Related Articles

Count Empty Cells in Excel (4 Ways)

Count One Column If Another Column Meets Criteria in Excel (4 Suitable Ways)

The COUNTIF Function to Count Blank Cells in Excel: 2 Examples

How to Count Rows with Text in Excel (Easiest 8 Ways)

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo