You can use the following methods to count the number of rows in a selection using VBA:
Method 1: Count Rows in Selection & Display Count in Message Box
Sub CountRowsInSelection()
MsgBox Selection.Rows.Count
End Sub
This particular example counts the number of rows in the current selection and then displays this number in a message box.
Method 2: Count Rows in Selection & Display Count in Specific Cell
Sub CountRowsInSelection()
Range("E1").Value = Selection.Rows.Count
End Sub
This particular example counts the number of rows in the current selection and then displays this number in cell E1.
The following examples show how to use each method in practice.
Example 1: Count Rows in Selection & Display Count in Message Box
Suppose we select the cell range A1:C17 in our spreadsheet:
We can create the following macro to count the number of rows in the selection and display the results in a message box:
Sub CountRowsInSelection()
MsgBox Selection.Rows.Count
End Sub
When we run this macro, we receive the following output:
The message box tells us that there are 17 rows in the current selection.
Example 2: Count Rows in Selection & Display Count in Specific Cell
Suppose we select the cell range A4:D9 in our spreadsheet:
We can create the following macro to count the number of rows in the selection and display the results in cell E1:
Sub CountRowsInSelection()
Range("E1").Value = Selection.Rows.Count
End Sub
When we run this macro, we receive the following output:
Cell E1 tells us that there are 6 rows in the current selection.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
How to Count Rows in Table Using VBA
How to Count Cells with Specific Text Using VBA
How to Count Unique Values in Range Using VBA
