Let VBA count rows, columns, and even sheets in your Excel workbooks.
Counting the number of rows or columns in the current selection is an easy task for VBA. All you need to know is the object model. Specifically, the Selection object returns the selected object in the active window. For example, the following subprocedures display the number of rows and columns, respectively, in the current selection in a message box:
MsgBox Selection.Rows.Count, vbOKOnly, "Rows"
MsgBox Selection.Columns.Count, vbOKOnly, "Columns"
Of course, the selection must be a valid range selection. If you select a chart object or anything other than a range, the procedures will return an error.
Similarly, to count sheets in the workbook, use this simple procedure:
MsgBox Application.Sheets.Count, vbOKOnly, "Sheets"
End SubUnlike the column and row counting procedures, this one doesn't evaluate a Selection object. Instead, it displays the number of sheets in the workbook, regardless of how you might have grouped the sheets. To return a value you can use in another procedure, change the subprocedure to a function procedure as follows:
Function CountRows() As Long
CountRows = Selection.Rows.Count
End FunctionFor example, if the current select is C11:F18, the function returns the value 8.