Merge multiple Ranges into one Array VBA

[johnmpl]

Greetings Mr. Excel community

This question is associated with Excel VBA code . How I can place multiple discontinuous ranges within Excel VBA array? The idea is to find an efficient method of doing , looking at what can be done without loops.

Example:

Rank 10 rows x 5 columns
Rank 7 Rows x 5 columns
Range 9 rows x 5 columns

Place in a continuous array of 26 rows by 5 columns.

The example is small, but must be efficient in the amount of resulting rows (about 10000+ rows ) .

Blessings !

 

[DocAElstein]

@Peter_SSss
I learned something very useful Thanks to your use of Cells as First Index Argument, thanks
Trouble writing huge array into worksheet range [SOLVED] - Page 2


@Jack Sht
Regarding your interest in handling a large Spreadsheet the above link may also be worth a look

Alan

 

[johnmpl]

Hi again Alan!

Thx for your response. I need an array because I want to merge lot of sheets (around 1000) into one sheet. I know that an array is a wonderful way to doy it... but construct is difficult. The idea is put all the data of each sheet in array (full), and later put it in a sheet all the data. I know do it by loops... but I don't want copy paste 1000 times. Thx for your help.

 

[johnmpl]

Hi again!

I try with Array of Arrays, and time is halved (from 40 secs to 20 secs aprox). Another way to do it? Thx.

 

[DocAElstein]

Hi again! I try with Array of Arrays, and time is halved (from 40 secs to 20 secs aprox).

Hi again johnmpl
Please share the codes you used, and tell us what your test data looked like
_ ..............................

... Another way to do it? Thx.

I still cannot think of a way to consolidate various ranges of data into one single Array without either some looping or using a help sheet where range data is first pasted. If No-one else can think of a way, maybe I will think of something in a couple of weeks when I may be back at my Excel Computers. ( As I said before ( Post #20 ) I am "away"just now)

Alan