Dynamic Array Sizing

[AlvaroSiza]

I'm sure it's something small, but I can't seem to get over the hump.

A worksheet range of 1 to n rows x 1 to 10 columns is passed to an array. First column is tested as matching a global named range within workbook (string = string). If TRUE, 7 of the 10 columns in a different order are passed to a second array.

How do I resize the 2nd array (1 to n + 1) to accept each new instance of TRUE above? I am currently running the same test twice, the first time to count the instances of TRUE so that I can just pre-size the array, and then again to pass the data in. This seems ineffecient to me.

Thanks,

AS

Please Login or Register  to view this content.

[Kyle123]

Can't you run a countif over the first column rather than a loop? That said, I'm not sure which would be faster. Looping over arrays is extremely fast, there's a discussion here on something similar http://stackoverflow.com/questions/1...rtain-criteria

EDIT: Actually, why not make the second array the same height as rngSource, you can then just resize the range you dump into to the matches found, that way it will only dump the top rows that match.

[JosephP]

you can also use an array of arrays for the second array and use redim preserve once at the end to resize it down to only the used elements

[AlvaroSiza]

Like this:

Please Login or Register  to view this content.

I'm getting subscript out of range on the ReDim Preserve line.

[Kyle123]

No, Joseph meant an array of arrays so a 1d array for the rows and multiple 1d arrays for the columns. You cant redim the first dimension of a multi dimensional array