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.


How to Count Rows with VBA in Excel: 5 Methods

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.

Read More: How to Count Rows with Data in Column Using VBA in Excel


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

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

Read More: Excel VBA: Count Rows with Specific Data


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

⧪  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

Read More: How to Count Rows with Multiple Criteria 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 from 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

⧪  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

Read More: How to Count Rows with Text 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

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

Read More: How to Count Rows in Selection Using VBA in Excel


Download Practice Workbook


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo