Results 1 to 4 of 4
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    clear multi-dimensional array (Excel 2003)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: clear multi-dimensional array (Excel 2003)

    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.

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: clear multi-dimensional array (Excel 2003)

    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

  4. #4
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: clear multi-dimensional array (Excel 2003)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •