Table of Contents
Counting the number of rows in a selected range using VBA (Visual Basic for Applications) is a simple and efficient way to obtain the total number of rows within a given range. This can be achieved by using the “Count” or “CountA” function in VBA, which counts the number of non-empty cells in a range. By specifying the range of cells to be counted, VBA will return the total number of rows, providing a useful tool for data analysis and manipulation. This method allows for a quick and accurate way to gather information about the size and scope of a selected range, making it a valuable tool for data management and organization.
VBA: Count Rows in Selection
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
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: