Wednesday, April 06, 2005

Using the Resize property in Excel VBA to change a range size

Jon and Rob both showed me how to get the same result as I got in yesterdays post using a For Next Loop by using the Resize property.
The Resize property operates on the range collection and resizes it (obviously) using the following syntax.
expression.Resize(RowSize, ColumnSize). Both the rowsize and columnsize numbers are optional.
Here is the revised code from yesterday.

Dim linetext, i
Sub write_lines()
'Macro written by www.spyjournal.biz April 2005
linetext = ActiveCell.Value
ActiveCell.Resize(100).Value = ActiveCell.Value
End Sub

In this case the expression is the ActiveCell and the the column numbers are ignored as the row numbers are 100.
For Next Loops are actually slower than this becuase there are multiple cycles required and that requires CPU time where the Resize property is much faster as it only operates once.
Thanks guys


Posted by: Jethro | 11:22 PM |