Thanks for the tip. I didn't know about the cost...

Relatively speaking, how costly is it?

Going further and given the example, is it less costly to dim the array to
65536 right up front, or to redim on the fly (as I did), or to count the
number of blanks first, then dim the array once (even though it would require
a little more code)?



"Chip Pearson" wrote:

If rCell.FormulaR1C1 < "" Then
lX = lX + 1
Redim Preserve saItems(lX)
saItems(lX) = rCell.FormulaR1C1

A ReDim Preserve operation is expensive. A better way is to ReDim the array
to the largest possible size before doing anything with the array, fill the
array with the apporpriate values, and then do a single ReDim Preserve at
the end to reduce the size of the array to the actual used size.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




"XP" wrote in message
...
Hi,

In this example, the array (saItems --- sa=string array in my shorthand)
is
redimensioned dynamically and only adds data from the cells in column
three
that are not blank :

Dim saItems() as String
Dim rCell as Range
Dim lX as Long
For Each rCell in ActiveSheet.UsedRange.Columns(3).Rows
If rCell.FormulaR1C1 < "" Then
lX = lX + 1
Redim Preserve saItems(lX)
saItems(lX) = rCell.FormulaR1C1
End If
Next

"Preserve" causes the array to hold all items previously loaded into the
array; without this keyword, the array would only hold one item, i.e. the
last one loaded.

HTH

"Mark Stephens" wrote:

Hi,

At the beginning when you define an array do you have to specify a size
or
can you just leave the () empty and then it will hold however many items
it
holds (or can you redim it when you know). A bit rusty on arrays, help
appreciatyed, regards, Mark