Table of Contents
Resize
The Resize property in Excel VBA makes a range a specific number of rows and columns smaller or larger. The Resize property always takes the top left cell of a range as the starting point.
Contract
To contract the size of a range specify a smaller number of rows or columns than the original range.
Place a command button on your worksheet and add the following code line:
Range(“A1:C4”).Resize(3 2).Select
Result:
Explanation: this code line resizes Range(“A1:C4”) to 3 rows and 2 columns and selects this range (border for illustration only).
Code line:
Range(“A1:C4”).Resize( 1).Select
Result:
Explanation: this code line resizes Range(“A1:C4”) to 1 column (we omit any row specification) and selects this range.
Expand
To expand the size of a range specify a larger number of rows or columns than the original range.
Code line:
Range(“A1:C4”).Resize( 5).Select
Result:
Explanation: this code line resizes Range(“A1:C4”) to 5 columns. The Select method selects Range(“A1:E4”).
Code line:
Range(“A1:C4”).Resize(10) = 99
Result:
Explanation: this code line resizes Range(“A1:C4”) to 10 rows. Instead of using the Select method you can work with the resized range directly. For example set the values of the cells in the resized range to 99.
