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