␡
- The Range Object
- Using the Top-Left and Bottom-Right Corners of a Selection to Specify a Range
- Referencing Ranges in Other Sheets
- Referencing a Range Relative to Another Range
- Using the Cells Property to Select a Range
- Using the Offset Property to Refer to a Range
- Using the Resize Property to Change the Size of a Range
- Using the Columns and Rows Properties to Specify a Range
- Using the Union Method to Join Multiple Ranges
- Using the Intersect Method to Create a New Range from Overlapping Ranges
- Using the IsEmpty Function to Check Whether a Cell Is Empty
- Using the CurrentRegion Property to Quickly Select a Data Range
- Using the Areas Collection to Return a Non-contiguous Range
- Next Steps
This chapter is from the book
This chapter is from the book
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.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
){kind=small}
Figure
3.13 The Areas Collection makes it easy to manipulate non-contiguous ranges.