most efficient and quickest way to populate data from sheet to array

Hi i was told the following is the most efficient way of getting data from a sheet into an array

dim arr as variant
arr = range("A1:J200").value

Using the above code my macro retrieves data from 4 different sheets(approx 200 rows 10 columns each sheet!!!) and then puts them in 4 different variants. I then use these 4 variants , manipulate data and put all of them into one variant which i then transfer on to one sheet at once.

i have also been told to avoid using variants as they are generally slow?! is that right?

What are some of the other ways i can achieve the above?

I was thinking of using for loops but i find them extremely slow. each worksheet cell to array is slow...

Please advise best approach based on your experiences.. Thanks in advance.

Originally Posted by AlphaFrog

I agree. This is probably the most efficient method without knowing how you "manipulate" the data.


No. Variants are not slow. Use them where appropriate as in this case.


This s probably the best method without knowing further details.


Loops themselves are not slow. What you do within the loop maybe slow. Looping through cells on a sheet is slow because each reference to a cell is expensive; not the loop itself. The idea is to minimize the read\writes to the sheet. Your code above only has 4 reads and 1 write. That's what makes it efficient.

Thank you. Your input appreciated!