Multidimensional Array vs (extended) One dimensional Array

[EchoPassenger]

Could anyone advise me which approach, if either, is a more efficient/better option...

I want to loop though some rows on a worksheet and add the values in 6 cells to an array. Would there be any advantage to either creating an array with 6 columns, or having a single column array and simply adding the next 6 values from each row underneath the previous. So for example, the values from two rows would create either a 2x6 array, or a 1x12 array.

It's the same amount of values, but I'm thinking that a single column array would be easier to navigate when performing subsequent actions and perhaps memory wise a single, albeit longer, column might be more efficient? I guess also a single column array could be better done as a collection, if that would make any difference?

Thanks in advance.

[mehmetcik]

Wrong Approach

Load an Array with 1200 Values

Please Login or Register  to view this content.

Loop through 1200 entries in the Array and count how many "Apples" are in the Array

Please Login or Register  to view this content.

Loop through 1200 entries in the Array and count how many "Apples" are in Column 3 of the Array

Please Login or Register  to view this content.

[EchoPassenger]

Thank you, very helpful. In terms of speed and efficiency though, would dealing with a single column array be any 'better' that a multi column array, or would there be no real difference?

[mehmetcik]

It really depends on what you are trying to do.

However if your Data is in 1200 rows of 6 columns what advantage would there be in listing your data into a one dimensional array of 7200 entries?

At the same time how would you find a specific value in the third column of an array of 1200 rows and 6 columns

Please Login or Register  to view this content.

[EchoPassenger]

Both the targeted rows and columns in my data are non contiguous.

So I will be looping down rows on the worksheet to find a certain value, then for any matching rows I will then add the values in say columns C, F, H, M etc. to the array.

I'm currently unsure exactly what actions I will need to then perform on the array. So potentially I won't need to target specific positions as per your second Apple count example.

It's not an issue for me to do it either way, but I was thinking for simplicity if I was to just add all the values one after another to a single column, I know they'll be in 'groups' of 6 so if I need to output back to a worksheet either all, or just the values in position 6 for example, then that will still be easy enough to do.

[mehmetcik]

So I will be looping down rows on the worksheet to find a certain value, then for any matching rows I will then add the values in say columns C, F, H, M etc. to the array.

Which column will you be looking at?

Do you realise that each time you read / write between VBA and Excel you are adding significant delays.

[EchoPassenger]

So is it better, for example, to just add all the values from columns C:M in each matching row to the array, and then just deal with the individual columns I need and ignore the others?

[mehmetcik]

Correct. Just try to keep it as simple as possible.

[EchoPassenger]

Great, thanks, I'll work on that principle then.