Home > Articles > Computer Software > Business Office Software > Spreadsheet Software > Microsoft Excel

This chapter is from the book

This chapter is from the book

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):

Set Rng = Range("B1:B16").Find(What:="0", LookAt:=xlWhole, LookIn:=xlValues)
Rng.Offset(, -1).Resize(, 2).Interior.ColorIndex = 15
Figure 3.3

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.

  • + Share This
  • 🔖 Save To Your Account