How can I "ReDim Preserve" a 2D Array in Excel 2007 VBA so that I can add rows, not columns, to the array?

Question

I'm working with a dynamic array in Excel VBA. The number of columns (m) is fixed, however, I do not know how many rows (n) will be required.

The help documents state that ReDim Preserve myArray(n, m) allows me to make m larger, but not n. However, I need to increase the number of rows (n) while preserving my data, not columns (m)!

For example, I may have a (5,20) array that I would like to expand to (10,20) while preserving my data.

It seems that if there were some way to transpose my array, do a ReDim Preserve to expand the number of "columns", then re-transpose my array, I could accomplish what I want.

Is this the correct way to do this? If so, how can I do that?

Is there a better way to accomplish what I want?

Answer 1

If you are developer - what is the difference between rows and columns? Using array(N, 2) (if you have 2 columns) is the same as array(2, N) - for which you can

ReDim Preserve arr(1 to 2, 1 to N+1). 

And the difference for you (as developer) will be to put the variable from the cycle in second place, instead of the first one:

N = ubound(arr)
FOR i=1 to N
    GetColumn1Value = arr(1, i)
    GetColumn2Value = arr(2, i)
NEXT i

Or you want this:

N = ubound(arr)
FOR i=1 to N
    GetColumn1Value = arr(i, 1)
    GetColumn2Value = arr(i, 2)
NEXT i

What is the difference?