The Areas Collection

An area is a contiguous (that is, connected) block of cells in a worksheet. There is no Area object in the Excel object model. Instead, areas are Range objects.

However, every range is made up of one or more areas, and the collection of all Range objects that represent these areas is the Areas collection for the range. To illustrate, consider Figure 19-18, which is the result of calling the following code:

Dim rng As Range

Set rng = ActiveSheet.Cells.SpecialCells(

xlCellTypeConstants, xlNumbers) rng.Select

Note that three distinct areas are selected.

Figure 19-18. A range with three areas

1

A

B

C

D

11

2

2

3

4

3

5

5

6

7

6

8

1

We can clear the second area by writing:

rng.Areas(2).Clear

This will clear the cells C4 and D4. (Areas is a 1-based collection.)

It strikes me as a bit risky to refer to an individual area by index. However, it is perfectly safe to cycle through all areas using a For loop such as:

Dim rng As Range, r As Range

Set rng = ActiveSheet.Cells.SpecialCells(

xlCellTypeConstants, xlNumbers) For Each r In rng.Areas

Debug.Print r.Cells.Count Next

0 0

Post a comment