Help with Declaring a dynamic 2 dimensional array

[gablet]

Hi Guys,

I am trying to declare a 2 dimensional array and have written the below code. the problem I have is that the upper bound I declare results in an array that is too big as I only want the rows that have cell (i,9) as purchases or redemptions (this needs to be amended first). I think I need to declare a dynamic array (as the rows could change) but I don't know how to do this with a two dimensional array. Any help is greatly appreciated

Please Login or Register  to view this content.

[shg]

Please Login or Register  to view this content.

[gablet]

Thanks shg

Looking at your post I assumed you meant declare the transarray as variant and then redim in the loop but this doesnt seem to work, if I put the Redim statement in the loop I am still left with a few thousand extra rows in the array with no data. I then tried the below and I got a type mismatch error when the code is populating the array

Please Login or Register  to view this content.

[shg]

No, I meant what I posted. finalrow is neither declared nor initialized in your code. The code needs other changes, but start with that. And putting Option Explicit at the top of the code module.

[gablet]

Sorry mate Im pretty new to VBA just bear with me. I've made changes below -

Please Login or Register  to view this content.

[shg]

Better, good job. How's that working for you?

[gablet]

Yeah it works better but the array's size is the same as the whole sheet (5000 rows). I want just the rows that have "purchases" or "redemptions" in (i,9) to determine the arrays size and then fill it under the 5 headings(6,5,9,19,16). There are around 1000 of these rows and when the array is currently filled it has around 4000 sets of empty values

[shg]

Maybe ...

Please Login or Register  to view this content.

[gablet]

That works great thanks shg

[shg]

You're welcome.