excel - Copy/PasteSpecial vs Range.Value = Range.Value

Question

I've read a number of times on this site (and elsewhere) that it's best to avoid copy/paste if possible in VBA macros. For example, instead of doing this...

For i = 1 To tbl.ListColumns.Count
    With tbl.ListColumns(i).DataBodyRange
        .FormulaR1C1 = "=2*1"
        .Copy
        .PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
    End With
Next

...it's supposedly better/faster to do this:

For i = 1 To tbl.ListColumns.Count
    With tbl.ListColumns(i)
        .DataBodyRange.FormulaR1C1 = "=2*1"
        .DataBodyRange = .DataBodyRange.Value
    End With
Next

But testing this out on a large table (15 columns, 100k rows), the copy/paste version was significantly faster (1.9 sec vs 2.7 seconds). The difference remained even if I declared tbl.DataBodyRange as a Range variable first.

I thought this might be some weird property of ListObjects, but the difference was actually even bigger without them:

'Runs in 1.1 seconds
With Sheet1.Range("A1:O100000")
    .FormulaR1C1 = "=2*1"
    .Copy
    .PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End With

'Runs in 2.1 seconds
With Sheet1.Range("A1:O100000")
    .FormulaR1C1 = "=2*1"
    .Value = .Value
End With

Does anyone know why the copy/paste method is so much faster? And are there additional reasons to avoid using copy/paste (assuming that the clipboard will never be used outside of Excel while the macro is running)?

EDIT: Here's the first set of test results comparing Copy/PasteValues to the array read/write method described by Mat's Mug in the accepted answer. I tested range sizes from 1000 cells to 1 million cells, incrementing by 1000 at a time, and took the average of 10 tests for each range size. Copy paste started slower, but quickly overtook the set value method (it's hard to see on the chart, but the break even point is ~15k cells).

I also ran 10 further tests at the lower end of the range (range sizes from 100 cells to 100000 cells, incrementing by 100 at a time) to try to pin down where the break even point was occurring. This time I used Charles Williams' "MicroTimer" instead of the default timer, hoping that it'd be more accurate for the sub-second timing. I also included both the "Set Array" version and the original ".Value = .Value" version (and remembered to switch calculation to Manual, unlike during the first set of tests). Interestingly, the array read/write method fared significantly worse this time around, with a break even point of around 3300 cells and worse peak performance. There was virtually no difference between array read/write and .Value = .Value, though the array version performed slightly worse.

Here's the code I used for the last round of tests:

Sub speedTest()
    Dim copyPasteRNG(1 To 10, 1 To 1000)
    Dim setValueRNG(1 To 10, 1 To 1000)
    Dim setValueArrRNG(1 To 10, 1 To 1000)

    Dim i As Long
    Dim j As Long
    Dim numRows As Long
    Dim rng As Range

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.DisplayStatusBar = False

    For i = 1 To 10
        numRows = 100
        For j = 1 To 1000
            Set rng = Sheet3.Range("A1:A" & numRows)
            setValueRNG(i, j) = getTime(False, rng, False)
            setValueArrRNG(i, j) = getTime(False, rng, True)
            numRows = numRows + 100
        Next
    Next

    For i = 1 To 10
        numRows = 100
        For j = 1 To 1000
            Set rng = Sheet3.Range("A1:A" & numRows)
            copyPasteRNG(i, j) = getTime(True, rng)
            numRows = numRows + 100
        Next
    Next

    Sheet4.Range("A1:J1000").Value2 = Application.Transpose(copyPasteRNG)
    Sheet5.Range("A1:J1000").Value2 = Application.Transpose(setValueRNG)

    Application.DisplayStatusBar = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

Function getTime(copyPaste As Boolean, rng As Range, Optional arrB As Boolean) As Double
    Dim startTime As Double
    Dim endTime As Double

    startTime = MicroTimer

    With rng
        .FormulaR1C1 = "=1"
        If copyPaste = True Then
            .Copy
            .PasteSpecial Paste:=xlPasteValues
            Application.CutCopyMode = False
        ElseIf arrB = True Then
            Dim arr As Variant
            arr = .Value2
            .Value2 = arr
        Else
            .Value2 = .Value2
        End If
    End With

    endTime = MicroTimer - startTime

    getTime = endTime

End Function

And here's the version of MicroTimer I used (in separate module):

Private Declare PtrSafe Function getFrequency Lib "kernel32" Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
Private Declare PtrSafe Function getTickCount Lib "kernel32" Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long

Private Const sCPURegKey = "HARDWAREDESCRIPTIONSystemCentralProcessor"
Private Const HKEY_LOCAL_MACHINE As Long = &H80000002
Private Declare PtrSafe Function RegCloseKey Lib "advapi32.dll" (ByVal hKey As Long) As Long
Private Declare PtrSafe Function RegOpenKey Lib "advapi32.dll" Alias "RegOpenKeyA" (ByVal hKey As Long, ByVal lpSubKey As String, phkResult As Long) As Long
Private Declare PtrSafe Function RegQueryValueEx Lib "advapi32.dll" Alias "RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal lpReserved As Long, lpType As Long, lpData As Any, lpcbData As Long) As Long

Function MicroTimer() As Double

    Dim cyTicks1 As Currency
    Static cyFrequency As Currency
     '
    MicroTimer = 0
    If cyFrequency = 0 Then getFrequency cyFrequency
    getTickCount cyTicks1
    If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency

End Function

Answer 1

Most (a lot, anyway) VBA macros don't "work with sets" and iterate the cells in a range. Not because it's a good idea (it's not), but because a lot simply don't know any better.

The fastest loop, when working with an object collection such as a Range, is a For Each loop. So I took your test, refactored it a bit, added tests for iterative solutions, and then I added an array read/write test, because that's also a common, good way to copy cell values.

Note that I pulled the formula-writing setup step out of the individual tests.

NOTE: This code takes control flow best practices and shoves them under the carpet. DO NOT use GoSub/Return in real code.

Sub Test()

    Const TEST_ROWCOUNT As Long = 10

    Const RANGE_ADDRESS As String = "A1:O" & TEST_ROWCOUNT
    Const RANGE_FORMULA As String = "=2*1"

    Dim startTime As Double

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Debug.Print "Testing with " & Sheet1.Range(RANGE_ADDRESS).Count & " cells (" & TEST_ROWCOUNT & " rows)"

    GoSub InitTimer
    TestPasteFromClipboard Sheet1.Range(RANGE_ADDRESS)
    Debug.Print "Pasting from clipboard, single operation:",
    GoSub ReportTime

    GoSub InitTimer
    TestSetRangeValue Sheet1.Range(RANGE_ADDRESS)
    Debug.Print "Setting cell values, single operation:",
    GoSub ReportTime

    GoSub InitTimer
    TestIteratePaste Sheet1.Range(RANGE_ADDRESS)
    Debug.Print "Pasting from clipboard, iterative:",
    GoSub ReportTime

    GoSub InitTimer
    TestIterateSetValue Sheet1.Range(RANGE_ADDRESS)
    Debug.Print "Setting cell values, iterative:",
    GoSub ReportTime

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    Exit Sub

InitTimer:
    Sheet1.Range(RANGE_ADDRESS).Formula = RANGE_FORMULA
    startTime = Timer
    Return
ReportTime:
    Debug.Print (Timer - startTime) * 1000 & "ms"
    Return
End Sub

Private Sub TestPasteFromClipboard(ByVal withRange As Range)
    With withRange
        .Copy
        .PasteSpecial Paste:=xlPasteValues
    End With
    Application.CutCopyMode = False
End Sub

Private Sub TestSetRangeValue(ByVal withRange As Range)
    withRange.Value = withRange.Value
End Sub

Private Sub TestIteratePaste(ByVal withRange As Range)
    Dim cell As Range
    For Each cell In withRange.Cells
        cell.Copy
        cell.PasteSpecial Paste:=xlPasteValues
    Next
    Application.CutCopyMode = False
End Sub

Private Sub TestIterateSetValue(ByVal withRange As Range)
    Dim cell As Range
    For Each cell In withRange.Cells
        cell.Value = cell.Value
    Next
    Application.CutCopyMode = False
End Sub

---

I had to reduce the range size by an order of magnitude (otherwise I'd still be staring at my non-responding Excel screen), but this was the output - of course the cell-by-cell iterative approach is MUCH slower, but notice how the clipboard figures compare to straight Value assignment:

Testing with 150 cells (10 rows)
Pasting from clipboard, single operation: 11.71875ms
Setting cell values, single operation:    3.90625ms
Pasting from clipboard, iterative:        1773.4375ms
Setting cell values, iterative:           105.46875ms

Testing with 150 cells (10 rows)
Pasting from clipboard, single operation: 11.71875ms
Setting cell values, single operation:    3.90625ms
Pasting from clipboard, iterative:        1718.75ms
Setting cell values, iterative:           109.375ms

Testing with 150 cells (10 rows)
Pasting from clipboard, single operation: 15.625ms
Setting cell values, single operation:    3.90625ms
Pasting from clipboard, iterative:        1691.40625ms
Setting cell values, iterative:           136.71875ms

So with 10 rows / 150 cells, copying the range into an array/assigning Range.Value is MUCH faster than the clipboard solution.

Obviously the iterative approaches are much slower, but notice how much slower the clipboard solution is, compared to directly assigning range values!

---

Time for another test run.

Testing with 1500 cells (100 rows)
Pasting from clipboard, single operation: 11.71875ms
Setting cell values, single operation:    7.8125ms
Pasting from clipboard, iterative:        10480.46875ms
Setting cell values, iterative:           1125ms

Testing with 1500 cells (100 rows)
Pasting from clipboard, single operation: 19.53125ms
Setting cell values, single operation:    3.90625ms
Pasting from clipboard, iterative:        10859.375ms
Setting cell values, iterative:           2390.625ms

Testing with 1500 cells (100 rows)
Pasting from clipboard, single operation: 15.625ms
Setting cell values, single operation:    3.90625ms
Pasting from clipboard, iterative:        10964.84375ms
Setting cell values, iterative:           1062.5ms

Much less clear-cut now, but dumping an array still seems the more reliably faster solution.

---

Let's see what 1000 rows gives us:

Testing with 15000 cells (1000 rows)
Pasting from clipboard, single operation: 15.625ms
Setting cell values, single operation:    15.625ms
Pasting from clipboard, iterative:        80324.21875ms
Setting cell values, iterative:           11859.375ms

I don't have the patience. Commenting-out the iterative tests.

Testing with 15000 cells (1000 rows)
Pasting from clipboard, single operation: 19.53125ms
Setting cell values, single operation:    15.625ms

Testing with 15000 cells (1000 rows)
Pasting from clipboard, single operation: 23.4375ms
Setting cell values, single operation:    15.625ms

---

Quite consistent; again, clipboard loses. But how about 10K rows?

Testing with 150000 cells (10000 rows)
Pasting from clipboard, single operation: 46.875ms
Setting cell values, single operation:    144.53125ms

Testing with 150000 cells (10000 rows)
Pasting from clipboard, single operation: 46.875ms
Setting cell values, single operation:    148.4375ms

Testing with 150000 cells (10000 rows)
Pasting from clipboard, single operation: 50.78125ms
Setting cell values, single operation:    144.53125ms

And here we are - clipboard clearly wins now!

---

Bottom line: if you have 100K cells to work with, clipboard is probably a good idea. If you have 10K cells to work with (or less), Value assignment array dump is probably the faster approach. Anything in-between probably requires benchmarking and testing to figure out the faster approach.

TL;DR: There's no silver bullet one-size-fits-all solution.

You'll want to avoid copy/pasting when you're working with a relatively small number of cells, and/or if you're iterating individual cells. For large, bulk operations involving a lot of data, clipboard isn't a crazy idea.

For the sake of completion:

Testing with 1500000 cells (100000 rows)
Pasting from clipboard, single operation: 324.21875ms
Setting cell values, single operation:    1496.09375ms

Testing with 1500000 cells (100000 rows)
Pasting from clipboard, single operation: 324.21875ms
Setting cell values, single operation:    1445.3125ms

Testing with 1500000 cells (100000 rows)
Pasting from clipboard, single operation: 367.1875ms
Setting cell values, single operation:    1562.5ms

For huge YUGE ranges, directly setting cell values seems to consistently outperform array dump, but the clipboard outperforms both, and by quite a margin.

So:

• Less than 100K cells: array dump / value assignment
• More than 150K cells: clipboard
• Anything in-between: array dump or clipboard, test to find out
• In no circumstance the faster approach is an iterative solution, by several orders of magnitude.