Home > Articles

Referring to Ranges

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,


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 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.3Figure 3.3 Resizing a range to extend the selection.

  • + Share This
  • 🔖 Save To Your Account