How do I use arrays in VBA to cut a massive amount of data from one sheet to the next
Hi
I need to loop through a large amount of data in one sheet and cut and paste a subset of it into another sheet. Currently I'm using a For Next loop to cut and paste individual rows of data, but it's very slow.
I would like to either copy all rows in one go to an array, and then paste it to the new sheet, or else copy to virtual memory, if that's at all possible.
Regards
JL
To give an example of an iterating loop see the snippet
I have used blocks of 40 datums but whilst not infinite much greater blocks can be used
Chris
Dim arr As VariantDim rng As RangeDim lngRow As Long For lngRow = 0 To 100 Step 10 Set rng = Sheets("Sheet1").Range("A" & lngRow + 1 & ":D" & lngRow + 10) Debug.Print rng.Address arr = rng Sheets(2).Range(rng.Address).Formula = arr Next
I have used blocks of 40 datums but whilst not infinite much greater blocks can be used
Chris
Open in new window