Referring to ranges

Using the Areas collection to return a noncontiguous range

The Areas collection is a collection of noncontiguous ranges within a selection. It consists of individual Range objects representing contiguous ranges of cells within the selection. If a selection contains only one area, the Areas collection contains a single Range object that corresponds to that selection.

You might be tempted to loop through the rows in a sheet and check the properties of a cell in a row, such as its formatting (for example, font or fill) or whether the cell contains a formula or value. Then you could copy the row and paste it to another section. However, there is an easier way. In Figure 3-10, the user enters the values below each fruit and vegetable. The percentages are formulas. The following line of code selects the cells with numeric constants and copies them to another area:

Set NewDestination = ActiveSheet.Range("I1")
For Each Rng In Cells.SpecialCells(xlCellTypeConstants, 1).Areas
    Rng.Copy Destination:=NewDestination
    Set NewDestination = NewDestination.Offset(Rng.Rows.Count)
Next Rng

FIGURE 3-10 The Areas collection makes it easier to manipulate noncontiguous ranges.