Fastest Method to Copy Large Number of Values in Excel VBA

Question

Quite simply, I am wondering what the fastest method of copying cell values from one sheet to another is.

Generally, I'll loop through the cells by column and/or row and use a line such as:

Worksheets("Sheet1").Cells(i,j).Value = Worksheets("Sheet1").Cells(y,z).Value

In other cases where my ranges are not consecutive rows/columns (e.g. I want to avoid overwriting cells that already contain data) I'll either have a conditional inside the loop, or I'll fill an array(s) with row & column numbers that I want to cycle through, and then cycle through the array elements. For example:

Worksheets("Sheet1").Cells(row1(i),col1(j)).Value = Worksheets("Sheet2").Cells(row2(y),col2(z)).Value

Would it be faster to define ranges using the cells I want to copy and the destination cells, then do a Range.Copy and Range.Paste operation? Is it possible to define a range using an array without having to loop through it anyway? Or will it be faster anyway to loop through an array to define a range and then copy-pasting the range instead of equating the cell values by looping?

I feel like it might not be possible to copy and paste ranges like this at all (i.e. they would need to be cells continuous through a rectangular array and pasted into a rectangular array of the same size). That being said, I would think that it's possible to equate the elements of two ranges without looping through each cell and equating the values.

Solution

For a rectangular block this:

Sub qwerty()
    Dim r1 As Range, r2 As Range

    Set r1 = Sheets("Sheet1").Range("A1:Z1000")
    Set r2 = Sheets("Sheet2").Range("A1")

    r1.Copy r2
End Sub

is pretty quick.

For a non-contiguous range on the activesheet, I would use a loop:

Sub qwerty2()
    Dim r1 As Range, r2 As Range

    For Each r1 In Selection
        r1.Copy Sheets("Sheet2").Range(r1.Address)
    Next r1
End Sub

EDIT#1:

The range-to-range method does not even require an intermediate array:

Sub ytrewq()
    Dim r1 As Range, r2 As Range

    Set r1 = Sheets("Sheet1").Range("A1:Z1000")
    Set r2 = Sheets("Sheet2").Range("A1:Z1000")

    r2 = r1
End Sub

this is really the same as:

ary=r1.Value
r2.value=ary

except the ary is implicit.

Comments

[BruceWayne]

Would it be quicker to copy/paste than to set the ranges equal?

[Gary's Student]

@BruceWayne Setting ranges equal (assuming the ranges are isomorphic) might be quicker, but the formatting would not come over..........but maybe that O.K.

[ma_YYC]

The first example makes sense, I understood how to do that initially; I was just wondering what the speed difference was compared to a loop. I figured it would be faster to do it that way than to loop through each cell.

[ma_YYC]

For the non-contiguous case, how would you define the ranges if they are originally arrays? That was half of the challenge since I'd think you might have to loop through that anyway. And where is the destination reference in your loop (since both ranges referred to are typed out as "r1"). I also noticed you referenced a Selection without defining one?

[ma_YYC]

I think I'd go with the "setting ranges equal" route that @BruceWayne suggested if I knew how to define ranges quickly from array values.

[Gary's Student]

@ma_YYC See my EDIT#1.

[ma_YYC]

That part makes sense. What I'm asking about is if I have [ary1 = {a1, a2, ... , aN}] and [ary2 = {b1, b2, ... , bN}], where the a's and b's are row numbers, how would I make those ranges that I can then use the [r1 = r2] method? Would the easiest/fastest way be to loop through them to generate cell addresses? The column numbers are known (i.e. static), it's just the rows that are variable (depending on if they are blank rows or not), which is why I can't define the ranges in a contiguous way.

[Vegard]

Maybe you can use his isomorphic approach still? Set rng = Range("A" & ary1(LBound(ary1)) & ":B" & ary1(UBound(ary1)) rng.SpecialCells(xlCellTypeVisible).Copy

[abakum]

For copy and paste into only visible cells look github.com/abakum/PasteInVisible

[Admin]

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

[abakum]

Look at GitHub