clear multi-dimensional array (Excel 2003)

[jujuraf]

For the life of me I can't remember how to clear a 2D (actually any-D) array. I have this "Dim aryList" and then I define it's size later as "Redim aryList(1 to 200, 1 to 3)". I populate this array twice for two different purposes so I'd like to reset it to only be the size of how many items I added (which I do in a For Loop). I kept a count of what I did add, say 120, so do I do "Redim Preserve aryList(1 to ct, 1 to 3)" where ct is the known count of how many elements are stored?

Also, how do I just clear it out completely? I tried aryList = nothing and a few other variations but they failed. Actually I just had an idea (dangerous I know) about creating a dummy, unused range (Dim rngNone as Range) and assign that to aryList (since it's really a range, right?)

aryList = rngNone

Thnx, Deb

[HansV]

To clear an array named aryList, use

Erase aryList

If the array has been declared with a fixed size, e.g. Dim aryList(1 to 10), Erase will initialize all elements to the default value for the data type (0 for numbers, "" for strings, Empty for variants, and Nothing for objects). In other words, each element still exists, but in its "default" state.
If the array has been declared dynamically, e.g. Dim aryList(), Erase will release all memory used by the array. In other words, all elements are removed.

To clear the items of a dynamic array without removing them, use ReDim aryList(1 To 10) or whatever size you want. Don't use the keyword Preserve, for that will keep the contents of existing values.

[jujuraf]

Ah... yes "Erase" I forgot about that as I rarely deal with these kind of arrays. Ok good deal, one problem fixed. I still wasn't able to resize the array after it's populated however. I initialize it to aryList(1 to 100, 1 to 3) and then use a For Loop to populate it. I want to then re-size it to only the size that I actually need (else the listbox that this list is used by scrolls for 100 items even when I only have 10, for example). I tried this:

Redim Preserve aryList(1 to ct, 1 to 3)

where 'ct' is the known # of entries in the array but it fails with "subscript out of range" error.

I assigned this to the listbox with

listBoxName.List = aryList

Originally I was using .AddItem but since this is a 3 col listbox, I wasn't able to populate it across the columns (I couldn't get the 2nd argument of .AddItem to work as I thought it would). Instead it made three rows for each single entry (Product, Price, Qty). That's why I'm doing the array to populate an entire row of the listbox with multiple columns instead of one row for Product, one for Price, one for Qty.

Thnx, Deb

[st3333ve]

With Redim Preserve, you can only resize the last dimension. This is one of several reasons for typically having the first dimension be the "columns" and the 2nd dimension be the "rows" (although I'm speaking as an Access/Word guy; I don't use Excel).