Counting Excel rows, columns, and sheets

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:

Sub CountRows()
  MsgBox Selection.Rows.Count, vbOKOnly, "Rows"
End Sub

Sub CountColumns()
  MsgBox Selection.Columns.Count, vbOKOnly, "Columns"
End Sub

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:

Sub CountSheets()
  MsgBox Application.Sheets.Count, vbOKOnly, "Sheets"
End Sub
Unlike 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 Function
For example, if the current select is C11:F18, the function returns the value 8.

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox