Home > Articles

Referring to Ranges

This chapter is from the book

This chapter is from the book

Using the Areas Collection to Return a Non-contiguous Range

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

Refer to Figure 3.12. You need to copy only the total numbers (the gray cells) and paste them to a new section. You might be tempted to loop through the sheet, copy a row, and paste it to another section. But there's an easier way (see Figure 3.13):

Figure 3.12Figure 3.12 You want to copy the gray cells 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.13Figure 3.13 The Areas Collection makes it easy to manipulate non-contiguous ranges.

  • + Share This
  • 🔖 Save To Your Account