Friday, April 20, 2012

Counting Rows & Columns & Sheets

When you have selected a range, it is sometimes useful to know how many rows or columns you have selected as this information

can be used in your macros (for eg when you have reached the end, you will know it is time to stop the macros. This macro

will do the trick.

Sub Count()
myCount = Selection.Rows.Count 'Change Rows to Columns to count columns
MsgBox myCount
End Sub

The next macro counts the number of sheets instead. Refer to Protecting all sheets macro which uses this method.

Sub Count2()
myCount = Application.Sheets.Count
MsgBox myCount
End Sub

No comments: