Redim Preserve Array

[Tracy]

Hi,

Please can someone explain how to redim a dynamic array.
I declare my array as follows:

Dim MyArray() as Variant

The brackets are empty so that it is a dynamic array. I want the array to have three columns and unsure of how many rows as yet (hence it's dynamic).

So I used the following line of code, to set the number of columns:

Redim Preserve MyArray(0,2)
MyArray(0,0) = "Tracy"
MyArray(0,1) = "Hill"
MyArray(0,2) = "Programmer"

But now I don't know how to Redim the array to keep the existing values, but to add the next row. SO I now want to add

MyArray(1,0) = "Fred"
MyArray(1,1) = "Smith"
MyArray(1,2) = "Manager"

but I need to Redim the array to create the next row of space to fit these details in, but I've tried everything and have no idea how to do this.

Any ideas??

p.s. It says in the help that you can't Redim Preserve to add new dimension, but iof this is the case then where are you meant to initially say how many dimensions there are?

Many thanks
Tracy

 

[ritchieroo]

Redim Preserve can only be used to resize the last dimension in your array, so do it this way around...

Dim MyArray() As Variant

Redim MyArray(2,0)
MyArray(0,0) = "Tracy"
MyArray(1,0) = "Hill"
MyArray(2,0) = "Programmer"

Redim Preserve MyArray(2,1)
MyArray(0,1) = "Fred"
MyArray(1,1) = "Smith"
MyArray(2,1) = "Manager"