Excel 2010 Change Range Size Using Macros
The Resize property allows 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)
> An easier way to create this range:
Range(“B3”).Resize(11, 3)
> If you only need to resize by a row or a column, you do not have to enter both the row and 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. Resizing just the rows is similar. Use either of the following:
Range(“B3”).Resize(RowSize:=2)
or
Range(“B3”).Resize(2)
> Again, both lines mean the same. It’s just a matter of code readability. From the list of produce, find the zero totals and color the cells of the total and corresponding produce:
Set Rng = Range(“B1:B16”).Find(What:=”0”, LookAt:=xlWhole, LookIn:=xlValues)
Rng.Offset(, -1).Resize(, 2).Interior.ColorIndex = 15
Note that the Offset property was used first to move the active cell over. When resizing, the upper-left corner cell must remain the same.
> Resizing can be used for single cells as well as for an existing range. For instance, if you have a named range but need it and the column next to it, use this:
Range(“Produce”).Resize(,2)
Keep in mind that the number you resize by is the total number of rows/columns you want to include.