Combining multiple arrays simply

[kev_]

I combine several large ranges from multiple near identical sheets in multipe workbooks
- for speed this is done using arrays
- each sheet contains the same number of columns, but row numbers vary
- example below comprises only 2 ranges to keep things simple
- looping through individual values in each sheet in each workbook and placing them directly in final array is slower than what I do already

Assigning values to arrays from ranges is easy
a() = rngA.Value
b() = rngB.Value

To combine the arrays
- run through all the values in each array and place them in the 3rd array before printing that to worksheet

Please Login or Register  to view this content.

Q1 Is there a slicker way to get the values into the final array?
- is it possible to assign multiple ranges in multiple workbooks directly to the final array
- is there an equivalent of the UNION function to glue 2 or more arrays together
( using UNION with the ranges first cannot work because on different sheets and workbooks)
- I have found various complex functions out there that will get the job done - including this very compehensive page from cpearson

Q2 - is there a quicker way to assign the values from the final array directly to the worksheet?
(Instead of printing each item individually to the worksheet - see bottom of code below)

Thank you for spending time reading this


Full code - run in attached workbook with {CTRL} k

Please Login or Register  to view this content.

[AB33]

Please Login or Register  to view this content.

I forget to include the result array

ReDim result(1 To rA + rB, 1 To cA)
Could be written like

Please Login or Register  to view this content.

[kev_]

Thank you AB33
That looks much faster that what I am doing

I am away from my PC , so cannot test, but....c is already an array (combined values)
Why not this?

Please Login or Register  to view this content.

EDIT:
This almost worked - but array c was zero based
see post#11 for what did work

[AB33]

I have removed one part of the loop. See module 2.
I have not done VBA code for over 3 months now. I am beginning to loss the syntax and plot. I am sure there are other better ways, but right now, I can not think in VBA mode.

[mjr veverka]

It can be shorter with only one 'For ... Next':

Please Login or Register  to view this content.

[kev_]

thank you @poruka vevrku
I will test your code tomorrow

[mjr veverka]

So, yet another variants ...

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[AB33]

If you fancy a non VBA solution, Power query (Append function) has an easy solution. You got to have excel 2016 though.

[Kenneth Hobson]

I normally use Chip Person's method. Here is Mike Erickson's method that works well too.

Please Login or Register  to view this content.

[kev_]

This is superb ... I will test each suggestion ... thanks everyone...

[kev_]

@AB33 - Thanks
- minor mods suggested by you provided a splendid simple solution to my second question

Q2 - is there a quicker way to assign the values from the final array directly to the worksheet?

1. Option Base 1 inserted at top of module to remove empty array values

2. To print array c to worksheet
Replaced:

Please Login or Register  to view this content.

With:

Please Login or Register  to view this content.

[kev_]

@poruka vevrku - you have been very busy - many variations - thank you
- I have not had time to understand them all in detail yet

I like this technique to print each array below the other

Please Login or Register  to view this content.

Amended to use inside a single loop, using a single array, my code is (thanks to you) both faster and simplified:

Please Login or Register  to view this content.

[kev_]

@Kenneth Hobson
- your suggested solution is very flexible and works well
- I am making a big effort to fully understand how the function truly works
- but the "old grey cells" are groaning under the enormity of that paricular task

Thank you for both the code itself and also this link to the thread which drove its construction
- following the thought process is proving invaluable

[AB33]

Hi Kev,
Yes, I actually amended the code in post 2.

ReDim result(1 To rA + rB, 1 To cA)

What you had was zero based, but you do not need to do base 0 as assigning a range in to an array would not make a difference: It is always a two dim with 1 as a lower bound.

[kev_]

Hi AB33 - your comments appear to be correct
but I need to revisit and test my code again to understand why I had to amend the code to make it work

When I used the code to print to worksheet...
- column A was blank and ...
- only 4 of the 5 columns were printed to worksheet

I "guessed" that was due to zero base
- Option Base 1 inserted at top of module "fixed" missing column

So that "fix" must have been because I changed something else
- thank you for pointing this out

EDIT:UPDATE
@AB33
- Option Base 1 removed and only 4 columns are written to the worksheet
- arrays a and b are base 1 (assigned from range)
- array c was not assigned from a range - so array c is zero based

[AB33]

Hi Kev,
ReDim c(rA + rB, cA)
Yes, when you are rediming like the above line, the lower bound is 0. Since range object does not have a zero index, it will print empty as a zero index and also skips the last upper bound.

To start at 1.

ReDim c(1 to rA + rB, 1 to cA)

My first code was also producing what you are described. I then realised the error and changed the redim.
Look at the attached on post 4. I have also amended your code (Module 1). If you run the code, you get the same output.

[kev_]

Thanks AB33
- that second look at the code helped me appreciate fully what was going on

[kev_]

During the next few days I will not have time to dedicate to examining the rest of your suggestions
- I will update the thread in due course
I already have more than 3 solutions
- so am marking thread as solved

Thank you all