Excel 2010 Bible

Chapter 44: VBA Examples

895

Identifying a multiple selection

Excel enables you to make a multiple selection by pressing Ctrl while you select objects or ranges.

This method can cause problems with some macros; for example, you can’t copy a multiple selec-

tion that consists of nonadjacent ranges. The following macro demonstrates how to determine

whether the user has made a multiple selection:

Sub MultipleSelection()

If Selection.Areas.Count > 1 Then

MsgBox “Multiple selections not allowed.”

Exit Sub

End If

‘ ... [Other statements go here]

End Sub

This example uses the Areas method, which returns a collection of all Range objects in the selec-

tion. The Count property returns the number of objects that are in the collection.

The following is a VBA function that returns TRUE if the selection is a multiple selection:

Function IsMultiple(sel) As Boolean

IsMultiple = Selection.Areas.Count > 1

End Function

Counting selected cells

You can create a macro that works with the selected range of cells. 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 num-

ber of cells in the current selection:

MsgBox Selection.Count

Caution
With the larger worksheet size introduced in Excel 2007, 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 exam-
ple, 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 (CountLarge) that uses the Double data type, which can han-
dle values up to 1.79+E^308.

For more on VBA data types, see upcoming Table 44.1.

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.