Excel VBA: Count Rows in a Sheet (5 Examples)

Counting the total number of rows in a sheet is quite easy for a smaller size of dataset. However, if the size of the data table becomes large, then it’s quite difficult to manually count all the rows in a sheet in Excel. But if you use the VBA script, you can easily get the count of the rows in a sheet. In this article, you will learn 5 different methods to count rows in a sheet using VBA in Excel.


Download Practice Workbook

You can download the Excel file from the following link and practice along with it.


5 VBA Examples to Count Rows in a Sheet in Excel

I will use the following dataset throughout the entire article to demonstrate all the methods to count rows in a sheet using VBA in Excel.

5 VBA Examples to Count Rows in a Sheet in Excel


1. Manually Select a Range to Count Rows in a Sheet Using VBA in Excel

If you want to manually select a range and then count rows in a sheet, then follow the steps below:

❶ Press ALT + F11 to open the VBA editor.

❷ Go to Insert > Module.

Insert a module to Manually Select a Range to Count Rows in a Sheet Using VBA in Excel

❸ Copy the following code:

Sub SelectRowsToCount()

Dim a As Range

Set a = Selection

MsgBox a.Rows.Count

End Sub

❹ Paste and save the code in the VBA editor.

Manually Select a Range to Count Rows in a Sheet Using VBA in Excel

❺ After that press ALT + F8 keys to avail the Macro dialog box.

❻ Select the function SelectRowsToCount and hit the Run button.

Open a macro to Manually Select a Range to Count Rows in a Sheet Using VBA in Excel

After that a little dialog box will appear and show the row number in your selection area just like the picture below:

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


2. Insert a Range to Count Rows in a Sheet Using VBA in Excel

If you are looking for VBA codes to input a range and count the number of rows in the given range, then follow the steps below:

❶ First of all, open the VBA editor by pressing ALT + F11 keys.

❷ Then create a new Module from the Insert tab.

❸  Now copy the code below:

Sub InputRangeToCountRows()

Dim a As Range

Set a = Range("B5:D12")

MsgBox a.Rows.Count

End Sub

❹ Take the code in the VBA editor and save it.

Insert a Range to Count Rows in a Sheet Using VBA in Excel

❺ Now press ALT + F8 keys to get the Macro window.

❻ From the Macro name list select InputRangeToCountRows and hit the Run button.

Select function name to Insert a Range to Count Rows in a Sheet Using VBA in Excel

Now you will get a small dialog box will the total number of rows in your input range as in the screenshot below:

Read More: Excel VBA: Count Rows in Named Range (4 Methods)


3. Add a Criteria to Count Rows in a Sheet Using VBA in Excel

In this method, I will show you to count rows based on criteria using VBA in Excel.

We will count all the rows having “AHF 500”. To do that follow the steps below:

❶ Open the VBA editor by pressing ALT + F8 keys.

❷ Go to Insert > Module.

❸ Copy the code below:

Sub CriteriaBasedRowCount()

Dim Count As Integer

Count = 0

For i = 1 To Selection.Rows.Count

If Selection.Cells(i, 1) = "AHF 500" Then

Count = Count + 1

End If

Next i

MsgBox Count

End Sub

❹  Paste and save the code in the VBA editor.

Add a Criteria to Count Rows in a Sheet Using VBA in Excel

❺  Press ALT + F8 to get the Macro dialog box.

From the Macro name list choose CriteriaBasedRowCount and hit Run.

Choose the function name to Add a Criteria to Count Rows in a Sheet Using VBA in Excel

After that, you will get the number of rows having “AHF 500” in them.

Read More: How to Count Rows with Multiple Criteria in Excel (6 Methods)


Similar Readings:


4. Input Text Values to Count Rows in a Sheet Using VBA in Excel

Using this method, you will get a pop-up dialog box to input text values. Based on the input data, the VBA script will return the number of rows that contain the input text.

Here are the steps to follow:

❶ Press ALT + F11 to open the VBA editor.

❷ Create a new Module from the Insert tab.

❸ Copy the following code:

Sub InputValuesToCountRows()

Dim Count As Integer

Count = 0

Dim mmm As String

mmm = InputBox("Input here: ")

Lmmm = LCase(mmm)

For i = 1 To Selection.Rows.Count

Words = Split(Selection.Cells(i, 1))

For Each j In Words

LWord = LCase(j)

If Lmmm = LWord Then

Count = Count + 1

End If

Next j

Next i

MsgBox Count

End Sub

❹ Paste and save the code in the VBA editor.

Input Text Values to Count Rows in a Sheet Using VBA in Excel

❺ Press ALT + F8 to open the Macro dialog box.

❻ From the Macro name choose InputValuesToCountRows and click on the Run button.

Open the Macro to Input Text Values to Count Rows in a Sheet Using VBA in Excel

❼ Insert text values in the pop-up dialog box and hit OK.

Finally, you will get the row number count in a small dialog box as in the picture below:

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


5. Count Rows Excluding the Blanks in a Sheet Using VBA in Excel

Use this method to count all the rows in a sheet excluding the blank rows.

❶ First, press ALT + F8 to open the VBA editor.

❷ Then go to Insert > Module.

❸ Now copy the following code:

Sub CountRowsExcludingBlanks()

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

❹ Paste and save the above code in the VBA editor.

Count Rows Excluding the Blanks in a Sheet Using VBA in Excel

❺ Press ALT + F8 to open the Macro dialog box.

❻ From the Macro name section choose CountRowsExcludingBlanks and hit the Run button.

After that you will get the count of the total number of rows in a sheet excluding all the blank rows just like the screenshot below:

Final Output: Count Rows Excluding the Blanks in a Sheet Using VBA in Excel

Related Content: How to Count Rows with Data in Excel (4 Formulas)


Things to Remember

  • Press ALT + F11 to open the VBA editor.
  • To open the Macro dialog box, press ALT + F8.

Conclusion

To sum up, we have discussed 5 ways to count rows in a sheet using VBA in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.


Related Articles

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo