How to Count Rows in Selection Using VBA in Excel

Get FREE Advanced Excel Exercises with Solutions!

In general, a typical Excel workbook contains numerous rows. And these rows may have blank or non-blank entries. Excel VBA count rows in selection is a handy trick to get a hold of any dataset.

Let’s say, we have a dataset where sold products are displayed with Order Date, Quantity, Unit Price, and Total Price. We want to count rows in selection using VBA in Excel.

Dataset-Excel VBA Count Rows in Selection

In this article, we demonstrate different variants of macros to Excel VBA count rows in selection.


â§­ Opening Microsoft Visual Basic and Inserting Code in the Module and Worksheet

Before proceeding to demonstrate any methods, it’s necessary to know the ways to open and insert a Module in Microsoft Visual Basic in Excel.


🔄 Opening Microsoft Visual Basic: There are mainly 3 ways to open Microsoft Visual Basic window.

🔼 Using Keyboard Shortcuts

 Press ALT+F11 altogether to open Microsoft Visual Basic window.

🔼 Using Developer Tab

 In an Excel worksheet, Go to the Developer Tab > Select Visual Basic. The Microsoft Visual Basic window appears.

developer tab-Excel VBA Count Rows in Selection

🔼 Using Worksheet Tab

Go to any worksheet, right-click on it > Choose View Code (from the Context Menu).

worksheet option-Excel VBA Count Rows in Selection


🔄 Inserting a Module in Microsoft Visual Basic: There are 2 ways to insert a Module in Microsoft Visual Basic window,

🔼 Using Context Menu

After opening the Microsoft Visual Basic window, Select a worksheet > right-click on it > Select Insert (from the Context Menu) > then choose Module.

select worksheet-Excel VBA Count Rows in Selection

🔼 Using Insert Tab

You can also do it by Selecting Insert (from the Toolbar) > then choosing Module.

insert module-Excel VBA Count Rows in Selection


🔄 Inserting Macro to Worksheet: Microsoft Visual Basic Module doesn’t perform exclusively for any individual worksheet. If you want macros to work exclusively for a worksheet, we have to insert the macro in the worksheet’s code.

🔼 Double Click on Sheet

In Microsoft Visual Basic window, double-click on any sheet as shown in the following image.

worksheet code-Excel VBA Count Rows in Selection

➤ The respective sheet code window appears. You can write a private macro code exclusively executable for the respective sheet.

worksheet code 2-Excel VBA Count Rows in Selection


Excel VBA to Count Rows in Selection: 10 Macro Variants

Excel VBA normally counts rows in selection with ease. However, we describe multiple macro variants to count rows considering different incidents we occasionally face working on Excel worksheets.


Method 1: Excel VBA to Count Rows in Selection

We have our dataset and the rows have no blanks in their entries. We can select any range as a selection to execute the macro.

Step 1: Before commencing the macro execution, select any range as shown in the below picture.

Method-1-Excel VBA Count Rows in Selection

Step 2: Open Microsoft Visual Basic, then, insert a Module using the instruction section. Paste the following macro in any Module.

Public Sub CountRows_Selection()
MsgBox Selection.Rows.Count
End Sub

Method-1-macro

 ➤ in the code,

1 – start the macro procedure by declaring the Sub name. You can assign any name to the code.

2 – the VBA Rows.Count statement displays the row count in a message box.

Step 3: Press F5 to run the macro. The macro takes you to the active worksheet and displays a message box. The message box displays the counted row number from the selection.

Method-1 result


Method 2: Count Rows in Selection of an Active Worksheet’s Used Range

Sometimes it’s quite annoying to select all the rows in a selection. To avoid this, we can automatically assign used rows of an active worksheet to the selection, then, the macro can count the used rows.

Step 1: Execute the instruction section to open and insert Module in Microsoft Visual Basic.

Sub CountRows_ActiveWS()
Dim i As Long
Dim wrkRng As Range
With ActiveSheet.UsedRange
For Each wrkRng In .Rows
If Application.CountA(wrkRng) > 0 Then
i = i + 1
End If
Next
End With
MsgBox "The count of rows in this used range is " & i
End Sub

Method-2-macro

 ➤ in the code,

1 – start the macro procedure by declaring the Sub name. Declare variables and assign the used range.

2 – operate a loop using VBA For Each in all the rows in a range.

3 – the COUNTA function is used to impose a condition using the VBA IF statement.

4 – count non-blank rows.

5 – display the count in a message box.

Step 2: Use the F5 key to run the macro. The macro ignores blank rows and counts the used rows and displays them in a message box.

Method-2 result

Read More: Excel VBA: Count Rows in a Sheet


Method 3: Count Rows from a Specific Worksheet and Range

On occasions, we may want to count rows from a specific range and worksheet. Excel VBA macros can count rows from a given range and worksheet.

Step 1: Repeat the instruction section to open and insert Module in Microsoft Visual Basic.

Sub CountRows_SRangeSWorksheet()
Dim wrkSheet As Worksheet
Set wrkSheet = Worksheets("Sale")
wrkSheet.Range("H5") = wrkSheet.Range("B5:F17").Rows.Count
End Sub

Method-3-macro

➤ From the above image, in the sections,

1 – begin the macro code declaring the VBA Macro Code’s Sub name.

2 – declare the variable as Worksheet and

3 – assign the Worksheet to a specific worksheet.

4 – display the row count of range (B5:F17) in the H5 cell of that specific worksheet.

Step 2: Hit F5 to run the macro. Afterward, return to the active sheet. You see the macro places the row count in the H5 cell as depicted in the image below.

Method-3-result

Read More: Excel VBA: Count Rows in Named Range


Method 4: Excel VBA to Count Rows with Blanks in Selection

What if there are blank rows present in the dataset? A variant of macro can count the rows ignoring blanks.

Suppose our dataset looks like the following image. And we want the row count ignoring blank rows for this dataset.

Method-4-Excel VBA Count Rows in Selection

Step 1: Select the entire range (i.e., B5:F17). Then, type the below macro code in the Module (inserted by following the instruction section).

Sub CountRows_BlankCells()
Dim RowNum As Integer
RowNum = 0
For i = 1 To Selection.Rows.Count
If Selection.Cells(i, 1) <> "" Then
RowNum = RowNum + 1
End If
Next i
MsgBox "The number of non blank row is " & RowNum
End Sub

Method-4-macro

➤ The code’s sections,

1 – initiate the macro procedure declaring the Sub name.

2 – declare the variables as Integer and assign the initial value of the variable to Zero (0).

3 – initiate a VBA For Next loop. The VBA IF statement checks each cell within selection have blank rows or not. Also, assign a formula to count rows in each iteration.

4– display the non-blank row number in a message box.

Step 2: To run the macro, Press F5. In a moment, the macro takes you back to the worksheet, you see the count of non-blank rows.

Method-4-result

You can verify the row count using the arrow indication on the left side of the above image.


Method 5: Count Rows Imposing a Criteria

Often, we have to look for entries that have a certain value. The values may exist in columns or rows. We can impose criteria to count the occurrences of the value.

Assume, we want to count the occurrence of entries more than $200 in the Total Price column.

Step 1: Highlight the entire Total Price column. Then, use the instruction section to open the Microsoft Visual Basic and insert a Module.

Method-5

Step 2: Write the subsequent macro in Microsoft Visual Basic Module.

Sub CountRows_Criteria()
Dim RowNum As Integer
RowNum = 0
For i = 1 To Selection.Rows.Count
If Selection.Cells(i, 1) > 200 Then
RowNum = RowNum + 1
End If
Next i
MsgBox "The row number having more than $200 is: " & RowNum
End Sub

Method-5-macro

➤ From the above image, the code’s sections,

1 – take forward the macro by setting the Sub name.

2 – declare the variable as Integer and assign the initial value of the variable to Zero (0).

3 – create a loop using VBA For Next. The VBA IF statement checks whether each cell within selection has a value more than $200 or not. Also, assign a formula to count rows for each iteration.

4– display the count of occurrences in a message box.

Step 3: In order to run the macro press F5.  Pressing F5 takes you to the worksheet and the occurrence count is displayed in a message box.

Method-5-result

If you want to cross-check the occurrences just count the arrows placed right side of the above image.

Read More: Excel VBA to Count Rows with Data


Method 6: Count Rows in Selection Containing a Specific Text

Now, similar to the value occurrence we can count specific string occurrences in a selection. For our dataset, we want to count specific product occurrences.

Step 1: Select the entire range from which you want to count a specific string from. Open Microsoft Visual Basic and insert a Module using the instruction section.

Method-6-Excel VBA Count Rows in Selection

Step 2: Use the following macro code in any Module of any Microsoft Visual Basic window.

Sub CountRows_SText()
Dim RowNum As Integer
RowNum = 0
Dim Text As String
Text = InputBox("Provide the Text String: ")
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
RowNum = RowNum + 1
End If
Next j
Next i
MsgBox "Product " & Text & " appears in the selection " & RowNum & " times"
End Sub

Method-6-macro

➤ The above image has code sections,

1 – take forward the macro by setting the Sub name.

2 – declare the variables as Integer and String. Assign the initial value of the RowNum variable to Zero (0) and Text to an Input box insertion.

3 – create a loop using VBA FOR. The VBA IF statement imposes a condition. The VBA LCase function is assigned to each entry in the selection.

4– display the count of occurrences in a message box.

Step 3: To run the macro press F5.  The macro displays a dialog box (i.e., Provide the text string) to insert a product name existing in the selection. Click on OK.

Method-6

➤ Providing the text string triggers the message box to display the row count as shown in the following image.

Method-6

For double-word products, just provide one word to count their occurrences in the selected range.

Read More: Excel VBA: Count Rows with Specific Data


Method 7: Count Non-Contiguous Rows in Selection from Specific Worksheet

Scenarios like the selection of scattered rows or non-contiguous rows pose difficulties to count rows. However, a private macro can do the job efficiently.

Step 1: Use the following macro in any worksheet’s code window (use the instruction section to insert macros in the worksheet’s code).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim wrkRng As Range
Dim i As Range
For Each wrkRng In Target.Areas
If i Is Nothing Then
Set i = wrkRng.EntireRow.Columns(1)
Else
Set i = Union(i, wrkRng.EntireRow.Columns(1))
End If
Next
Sheet1.Range("H5") = i.Cells.Count & " rows selected"
End Sub

Method-7-macro

➤ The code is marked in parts,

1 – begin the macro by setting the Private Sub name with a variable.

2 – assign the variables as Range.

3 – create a loop and VBA IF assigns a condition to execute different formulas assigned to variables. The VBA Union method adds each non-contiguous row in selections.

4 – insert the row count in the H5 cell of a given sheet (i.e., Sheet1).

Step 2: Return to the referred worksheet. Try to select multiple ranges as depicted in the below picture. The total selected row number is displayed in the H5 cell instantly.

Method-7-result


Method 8: Using Custom Function to Count Rows from a Range

A VBA custom function also counts the rows of a given range. To build a custom formula with defined arguments, we can use a couple of lines of macro.

Step 1: Write the following macro in a Module of Microsoft Visual Basic window. Use the instruction section to insert the Module and open the Visual window.

Function RowCount(Rng As Range) As Long
Dim P As Range
Dim CuntdRows As String: CuntdRows = ","
For Each P In Rng
If Not InStr(CuntdRows, "," & P.Row & ",") > 0 Then
CuntdRows = CuntdRows & P.Row & ","
End If
Next P
Dim Rows() As String: Rows = Split(CuntdRows, ",")
RowCount = UBound(Rows) - LBound(Rows) - 1
End Function

Method-8-macro

➤ The code is marked in parts,

1 – begin the macro by setting the Function name and its arguments.

2 – declare variables as Range and String.

3 – create a loop and VBA IF inserts a condition to find positions of occurrences using the VBA InStr function. Assign a formula for the CuntdRows variable.

4 – declare the Rows variable for the final formula as String and assign its value.

5 – the UBound and LBound functions determine the size of an array and pass it to the function.

Step 2: Now, back to the worksheet. Start typing the function name (i.e., RowCount) in the formula bar. You see the new custom build function pops up similar to the image below. Double click on the function.

Method-8-result

Step 3: Provide the range (i.e., B5:F17) to the function and press ENTER. You see the row count appears in the H5 cell.

custom function result


Method 9: Displaying Last Used Row Number of a Dataset

Normally Excel datasets contain hundreds of rows and it’s frequent we have to jump to the last row for obvious reasons. Keyboard Shortcuts can be used to jump directly to the last row. However, in this method, we demonstrate a VBA macro to display the last used row number in the dataset.

Step 1: To display the last row number, use the following code in any Microsoft Visual Module.

Sub DisplayLast_RowNum()
Dim RowNum As Integer
RowNum = Range("B5").End(xlDown).Row
MsgBox " The last used row number is " & RowNum
End Sub

Method-9-macro

➤ The code is marked in parts,

1 – begin the macro by setting the Sub name.

2 – declare the variable as Integer.

3 – assign the formula to jump directly to the last used row.

4 – display the last used row number in a message box.

Step 2: F5 key is used to run the macro. As a result, the macro takes you to the worksheet. In the worksheet, a message box displays the last used row number.

Method-9-result


Method 10: Displaying Last Used Row Number of a Specific Column

In addition to the previous method, we can display the last row number for a specific column.

Step 1: Insert the following macro in any Microsoft Module.

Sub LastRow_SPColumn()
Dim RowNum As Integer
RowNum = Cells(Rows.Count, 2).End(xlUp).Row
MsgBox "The last used row of column 2 is " & RowNum
End Sub

Method-10-macro

➤ The code is marked in parts,

1 – begin the macro by setting the Sub name.

2 – declare the variable as Integer.

3 – assign the formula to the VBA CELLS property and End.Row statement to display the last row of Column 2.

4 – display the last used row number of column 2 in a message box.

Step 2: Hit the F5 key to run the macro. In a moment, the macro takes you to the worksheet. Then a message box displays the last row number as shown in the picture.

Method-10-result


Download Excel Workbook


Conclusion

In this article, we’ve demonstrated multiple variants of macros to Excel VBA count rows in selection. Each macro we demonstrated has its own convenience depending on the data type. Hope these above-mentioned methods serve your purpose. Comment, if you have further inquiries or have anything to add.


Related Article

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Maruf Islam
Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo