Excel.Range Resize method

Ever need to add rows, delete  columns, and/or subtract rows in an range?   Well, the Resize method of the Excel.Range object is just the tool in Excel's expansive toolbox.   It can either expand or contract an existing range  – by altering the  either the number of columns and/or number of rows contained in the source range.

How to use the Resize method

To use, follow this  syntax found on MSDN

Range object.Resize(Desired Rowsize, Desired Column Size). 

Although, the syntax for using the Resize method seems straightforward, there are some potential gotchas.  To overcome them, follow these tips:

• The ColumnSize argument can’t be zero

• The RowSize argument can’t be zero

• In invalid value will trigger the Excel VBA error: Run-time error ‘1004’ Application-defined or object-defined error

 

Some example are:

Code	Note
range("A1:B2").Name="MyRange"	Create the range MyRange on the activesheet( 2 rows, 2 columns)
names("MyRange").RefersToRange.resize(Columnsize:=3).Select	Expand MyRange to contain 3 columns
names("MyRange").RefersToRange.resize(1).Select	Contract MyRange  to 1 column
Range("b1").resize(-1).select	Purposely generate VBA error 1004

 

See also

http://www.wiseowl.co.uk/blog/s160/resize-method-excel-vba.htm