Excels and Arrays (Resolved)

Here's the setup, I am using arrays to store data to perform calculations in memory and then assigning the array back to the range. It's impressively quick when compared to usual excel calculation speeds.

So it's something like: (never mind the syntax this is not my main problem, but just to let you know the process)

Array = Range ("B11", "AC1200")

So first array dimension has my rows (11 to 1200), and the second array dimension has my collumns (B to AC).

I want to added a new record, or row to my table (anywhere, beginning or end is fine), but can't redim my array in my first dimension.) It lets me add extra collumns, but not rows.

I am aware that I can always write the extra row directly to the worksheet but it I still need to do more data manipulation before pasting the data.

Anyone with a solution that I can try?

Thanks

I think you can only Redim the last dimension, so you would have to convert your array to change the dimensions.

Can I tranpose my array? Such that my first dimension becomes my last dimension. First dimension has 1 to 867 items, Second Dimension has 1 to 27 items.

I assume this would mean transferring the data to a separate array in order to do the move.

Any ideas on how to code this?

Thanks.

- that's what I get for trying to use an array as a grid control or database....

Nothing easier than that:

OriginalArray (1 to A, 1 to B)

NewArray (1 to B, 1 to A)

VB Code:

for i=1 to A
    for j=1 to B
        NewArray(j,i)=OriginalArray(i,j)
    next j
next i