ReDim Question

[paul_0722]

I just can't seem to get my head around ReDim Preserve. Other languages have a nice array.count function that you just call and then re-dimension your array to whatever it returns - I guess that's just not the case with Excel VBA unless you write a special function to do so (and I don't want to) so my thinking is I'll redimension and preserve data within the loop I'm using to create a second array... am I on the right track here?

My immediate dilemma - I have two 2 multi dimension arrays and I need to pull certain records from Array 1 when the value equals 'x' and place them in Array 2. I have no way of knowing the what first index dimension value will be needed for Array 2. I keep getting a 'subscript out of range' error if I use ReDim, but it works fine if I dimension the array with a good guess of how many are needed. I'm obviously using ReDim wrong... where should it go within the loop?

The attached code throws the error, but if you uncomment the Dim
ar02() code at the top and use ar02(15,5) and then comment out the ReDim Preserve within the loop - it will work and return worksheet "Sheet1" as expected - albeit with a few too many rows...

Any help appreciated!

[Bob Phillips]

You can only ReDim a single dimension of an array, the final dimension, so there is a real problem there as you are trying to ReDim both dimensions.

You then have to realise that an array starts at index 0 unless you set Option Base 1 at the start of the module.

Also your code tries to write to element ar02(d,5) before you get to c=5, so you haven't ReDim'ed ar02 to 5 elements.

[paul_0722]

Many thanks for good advice - I didn't realize I was so far off on several fronts! I'll change the base to 1. I want redim only the first dimension index from 15 down to 9 in this case (2nd dimension index stays at 5). Not sure what you mean by the "final" dimension - can that be either the first or second index?

Thanks again - really appreciate the help.

[Bob Phillips]

That is what I mean, you can't redim the first dimensikon, only the final, the second in your case.

[paul_0722]

Ok, second dimension. Thanks