- 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
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
Range.Resize(RowSize, ColumnSize)
To create a range B3:D13, use this:
Range("B3").Resize(RowSize:=11, ColumnSize:=3)
or, simpler:
Range("B3").Resize(11, 3)
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,
Range("B3").Resize(ColumnSize:=2)
Range("B3").ReSize(,2)
Both lines mean the same. The choice is yours. Resizing just the rows is similar:
Range("B3").Resize(RowSize:=2)
Range("B3").Resize(2)
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 cells—it 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:
Range("Produce").Resize(,2)
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.