Excel 2019 Bible

Chapter 47: Seeing Some VBA Examples

47

You may want to work with multiple selections rather than just ignore them, for example,

if you want to copy the selection. You can loop through the Areas collection of the Range

object as this example shows:

Sub LoopAreas()

Dim Area As Range

Dim Cell As Range

For Each Area In Selection.Areas

'copy each selection 10 columns to the right

Area.Copy Area.Offset(0, 10)

Next Area

End Sub

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 number of cells in the current selection:

MsgBox Selection.Count

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 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, CountLarge, which uses the Double data type that can handle values up to
1.79+E^308.

For more on VBA data types, see Table 47.1.

Bottom line? In the 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 num-

ber of cells in the data range to a variable named CellCount:

CellCount = Range("data").Count

You can also determine how many rows or columns are contained in a range. The following

expression calculates the number of columns in the currently selected range:

Selection.Columns.Count