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:

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:


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.

0 0

Post a comment