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

You can use:

dim myArr() as long 'variant/string...

and redim it when you the dimensions.

redim myArr(1 to 5, 7 to 9, -1 to 1)
(as a weird example)

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

--

Dave Peterson

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
>
>
>

> 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" <(E-Mail Removed)> wrote in message
news:0572D711-05DB-4B3D-A00A-(E-Mail Removed)...
> 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
>>
>>
>>

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" <(E-Mail Removed)> wrote in message
> news:0572D711-05DB-4B3D-A00A-(E-Mail Removed)...
> > 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
> >>
> >>
> >>
>

Dear Chip, Dave and HTH,

Thank you all for your help, Iit has all come folooding back and I now feel
competent with arrays again. FYI I am going to adopt a combination of all
your suggestions and count the number of cells in the column (which is the
number of items in my single dimension array) and then do one redim of the
array, thank you all once more, kind regards, Mark


"XP" <(E-Mail Removed)> wrote in message
news:BDF02D60-6658-442A-977F-(E-Mail Removed)...
>
> 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" <(E-Mail Removed)> wrote in message
>> news:0572D711-05DB-4B3D-A00A-(E-Mail Removed)...
>> > 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
>> >>
>> >>
>> >>
>>