Microsoft Office Tutorials and References
In Depth Information
Working with Ranges
This example, named prompt for a range.xlsm , is available on the companion
Specifying a Type argument of 8 for the InputBox method is the key to this procedure. Also,
note the use of On Error Resume Next . This statement ignores the error that occurs if the
user clicks the Cancel button. If so, the UserRange object variable isn’t defined. This example
displays a message box with the text Canceled . If the user clicks OK, the macro continues.
Using On Error GoTo 0 resumes normal error handling.
By the way, you don’t need to check for a valid range selection. Excel takes care of this task for you.
Make sure that screen updating isn’t turned off when you use the InputBox method to
select a range. Otherwise, you won’t be able to make a worksheet selection. Use the
ScreenUpdating property of the Application object to control screen updating
while a macro is running.
Counting selected cells
You can create a macro that works with the range of cells selected by the user. Use the Count
property of the Range object to determine how many cells are contained in a range selection (or
any range, for that matter). For example, the following statement displays a message box that
contains the number of cells in the current selection:
With the larger worksheet size in recent versions of Excel 2010, the Count property can
generate an error. The Count property uses the Long data type, so the largest value
that it can store is 2,147,483,647. For example, if the user selects 2,048 complete
columns (2,147,483,648 cells), the Count property generates an error. Fortunately,
Microsoft added a new property beginning with Excel 2007: CountLarge . CountLarge
uses the Double data type, which can handle values up to 1.79+E^308.
Bottom line? In the vast majority of situations, the Count property will work fine. If
there’s a chance that you may need to count more cells (such as all cells in a
worksheet), use CountLarge instead of Count .
If the active sheet contains a range named data , the following statement assigns the number of
cells in the data range to a variable named CellCount :
CellCount = Range(“data”).Count