- The Range Object
- Syntax to Specify a Range
- Named Ranges
- Shortcut for Referencing Ranges
- Referencing Ranges in Other Sheets
- Referencing a Range Relative to Another Range
- Use the Cells Property to Select a Range
- Use the Offset Property to Refer to a Range
- Use the Resize Property to Change the Size of a Range
- Use the Columns and Rows Properties to Specify a Range
- Use the Union Method to Join Multiple Ranges
- Use the Intersect Method to Create a New Range from Overlapping Ranges
- Use the ISEMPTY Function to Check Whether a Cell Is Empty
- Use the CurrentRegion Property to Select a Data Range
- Use the Areas Collection to Return a Noncontiguous Range
- Referencing Tables
- Next Steps
From the author of
From the author of
From the author of
Use 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 the selection contains only one area, the Areas collection contains a single Range object corresponding 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 code selects the cells with numerical constants and copies them to another area:
Range("A:D").SpecialCells(xlCellTypeConstants, xlNumbers).Copy Range("I1")
Set NewDestination = Range("I1")
For each Rng in Cells.SpecialCells(xlCellTypeConstants, xlNumbers).Areas
Rng.Copy Destination:=NewDestinations
Set NewDestination = NewDestination.Offset(Rng.Rows.Count)
Next Rng
)
Figure 3.10. The Areas collection makes it easier to manipulate noncontiguous ranges.