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.