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.
In this article, we demonstrate different variants of macros to Excel VBA count rows in selection.
Download Excel Workbook
⧭ 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.
🔼 Using Worksheet Tab
Go to any worksheet, Right-Click on it > Choose View Code (from the Context Menu).
🔄 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.
🔼 Using Insert Tab
You can also do it by Selecting Insert (from the Toolbar) > then Choosing Module.
🔄 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.
➤ The respective sheet code window appears. You can write a private macro code exclusively executable for the respective sheet.
10 Macro Variants of Excel VBA to Count Rows in Selection
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 in 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.
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
➤ 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 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
➤ 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.
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 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
➤ 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 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.
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
➤ 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 – create a loop using VBA FOR. 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.
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.
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➤ 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. 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.
If you want to cross-check the occurrences just count the arrows placed right side of the above image.
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.
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➤ 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.
➤ Providing the text string triggers the message box to display the row count as shown in the following image.
For double word products, just provide one word to count their occurrences in the selected range.
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➤ 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 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➤ 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.
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.
