dynamic arrays, redim preserve

Hello Pjjclark,

The ReDim statement works only on the last array dimension of a mutli-demsional array. You have solved your problem by creating multiple single arrays you can redimension. It would be nice to have a single array you could do this to, but that functionality doesn't exist.

Sincerely,
Leith Ross

Thanks for that Leith Ross.

I'll strike it off my 'this is bugging me' list.

Cheers

Paul

Paul

As it is possible to only redim the last column, one thing you could is is create a 5 row, variable column array. You can then change increase the last column. If you really need the output to be in a 5 column x row format, you can use the worksheet.transpose function to convert.

I don't know how far you can take it. I did a quick test up to 500 columns and it didn't have any problems.

hth

rylo

Thanks for that, and I'm intreeged.

I can see how it would work, the only further help I would appreciate is when it comes to the syntax of the 'redim preserve':

I assume you would set it up:

---

dim newarray()

ix as integer

ix = 1

redim newarray(5,ix)

code stuff

redim Preserve newarray(uboound(newarray(2,ix)+1)

---

It is the above line that I get a 'subscript out of range'

Thanks

Hi

Say you have data in the range A1:B19.

Please Login or Register  to view this content.

The code above will put it into a 2 dimensional array, with 2 rows and a variable number of columns. The first msgbox will show you the upper size of the second dimension (19)

You can then change it from a 2x19 to a 19x2 array with the transpose.

The second messagebox shows the ubound of the 1st dimension (20 - includes the default 0 that is not filled)

Hope that makes sense

rylo