- Jul 2, 2004
- 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
Using the Resize Property to Change the Size of a Range
The Resize property enables you to change the size of a range based off the location of the active cell. You can create a new range as you need it.
The syntax for the Resize property is
To create a range B3:D13, use this:
But what if you need to resize by only a row or a column, not both? You don't have to enter both the row and column parameters. If you need to expand by two columns,
Both lines mean the same. The choice is yours. Resizing just the rows is similar:
Once again, the choice is yours. It is a matter of readability of the code.
From the list of produce, find the zero total and color the cells of the total and corresponding produce (see Figure 3.3):
Set Rng = Range("B1:B16").Find(What:="0", LookAt:=xlWhole, LookIn:=xlValues) Rng.Offset(, -1).Resize(, 2).Interior.ColorIndex = 15
Notice that that the offset property was used first to move the active cell over; when resizing, the top-left corner cell must remain the same.
Resizing isn't only for single cellsit can be used to resize an existing range. For example, if you have a named range but need it and the two columns next to it, use this:
Remember, the number you resize by is the total number of rows and/or columns you want to include.
Figure 3.3 Resizing a range to extend the selection.