VBA - Array - Two Dimensional Array Issue

[VBAye]

Hi,

I have two columns of data that I want to extrapolate out but struggling to get the final bit of code to complete it.

I understand running this as an array will be the best option as the final row count once completed will be over 100,000.

In a basic example I have 3 rows of data in column A and 2 rows of data in column B. eg:

A B
Apple Farm
Orange House
Banana

The number of rows for the final product will be 6 (3 x2)
The final outcome should look like this, Where each A should be linked to a B:

A B
Apple Farm
Apple House
Orange Farm
Orange House
Banana Farm
Banana House

My code so far is:

Do I need a For Each element in arrLoc type of situation to loop this?
I tried various things but obviously they were off the mark, and I am sure the solution is probably easier than I think. Maybe I am overthinking it becuase it's an array.

Any help to complete the loop would be most appreciated.

Also, for the actual data I am using the final result could be between 100 and 200k rows. Will an array hold that much information and if so will this still take a very long time to process?

Thanks very much.

[jindon]

For counter loop

Please Login or Register  to view this content.

[VBAye]

Jindon,

that is perfect, thank you so much.

I will sit down and study this line by line and understand it, but in the meantime it does the trick.

Are there any size/row limitations on this?

[jindon]

Please Login or Register  to view this content.

Limit will depends on memory of your machine, and when size exceeds the number of rows in Excel, you will get run time error.

If this takes a lot of time in you actual data size, MS Query would be the second consideration.

[VBAye]

Thanks for the updated code with comments.
This only appears to work for the 3x2 example I gave you, if I fill in one more cell in either A or B then I get a Run Time error 9.
It looks like your code should pick up the current region despite the number of rows filled in, however this doesn't seem to happen. I appear to be locked in to the 3x2 matrix only.
Is this an issue my end where the current region is not refreshing or not being read correctly?

[VBAye]

Just an additional note to the above ... looks like I am maxxed out at having 2 values in column B but unlimited in A.
Does the following restrict this: ReDim b(1 To UBound(a, 1) * UBound(a, 2), 1 To 2)?
The error appears in: b(n, 1) = a(i, 1): b(n, 2) = a(ii, 2)

[jindon]

Try change to

Please Login or Register  to view this content.

Im out at the momemt, so there might be typo etc,

[jindon]

Oops, it is not that line.

should be

redim b(1 to ubound(a,1) ^ 2, 1 to 2)

fixed a typo.

[Andy Pope]

Try this

Please Login or Register  to view this content.

[VBAye]

Thanks Andy,

That is exactly what I am after, goes the trick perfectly.
I need to familiarize myself more in how to use Resize correctly.

Thanks again