- 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 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. The syntax for the Resize property is
Range.Resize(RowSize, ColumnSize)
To create a range B3:D13, use the following:
Range("B3").Resize(RowSize:=11, ColumnSize:=3)
Or 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 do not have to enter both the row and the column parameters.
If you need to expand by two columns, use one of the following:
Range("B3").Resize(ColumnSize:=2)
or
Range("B3").Resize(,2)
Both lines mean the same thing. The choice is yours. 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, find the zero totals and color the cells of the total and corresponding produce (see Figure 3.3):
){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 was used first to move the active cell over. 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.