- The Range Object
- Syntax for Specifying a Range
- Named Ranges
- Shortcut for Referencing Ranges
- 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 Select a Data Range
- Using the Areas Collection to Return a Noncontiguous Range
- Referencing Tables
- 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 on the location of the active cell. You can create a new range as needed. This is the syntax for the Resize property:
Range.Resize(RowSize, ColumnSize)
To create the range B3:D13, use the following:
Range("B3").Resize(RowSize:=11, ColumnSize:=3)
Here’s a simpler way to create this range:
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 the column parameters.
To expand by two columns, use either of the following:
Range("B3").Resize(ColumnSize:=2)
or
Range("B3").Resize(,2)
Both lines mean the same thing. The choice is yours. If you use the second line, make sure to include the comma so Excel knows the 2 refers to the ColumnSize argument. Resizing just the rows is similar. You can use either of the following:
Range("B3").Resize(RowSize:=2)
or
Range("B3").Resize(2)
Once again, the choice is yours. It is a matter of readability of the code.
From the list of produce, say that you want to find the zero totals and color the cells of the total and corresponding produce (see Figure 3.3). Here’s what you do:
){kind=link}
Set Rng = Range("B1:B16").Find(What:="0", LookAt:=xlWhole, _
LookIn:=xlValues)
Rng.Offset(, -1).Resize(, 2).Interior.ColorIndex = 15
Figure 3.3 Resizing a range to extend the selection.
Notice that the Offset property first moves the active cell over to the produce column. When you are resizing, the upper-left-corner cell must remain the same.
Resizing isn’t only for single cells; you can use it to resize an existing range. For example, if you have a named range but need it and the column next to it, use this:
Range("Produce").Resize(,2)
Remember, the number you resize by is the total number of rows/columns you want to include.