VBA Paste Values on large ranges

[MS_Xsmell]

I have always assumed using rg.Value2 = rg.Value2 was significantly faster than rg.copy: rg.PasteSpecial xlPasteValues. However, I'm questioning this assumption as I test a large block of formulas on a worksheet.

Why I am seeing better results using copy/paste values (6 seconds to run vs 4 seconds)? Any better approaches to hard code large blocks of active formulas than these?

Dim rg as Range
set rg = Range("Y18:EO8000") 'giant range of a live formulas I want to hard-code
rg.Copy: rg.PasteSpecial xlPasteValues

Dim rg as Range
set rg = Range("Y18:EO8000") 'giant range of a live formulas I want to hard-code
rg.Value2 = rg.Value2

 

[aaewalsh]

The reason you may be seeing better results using the copy and paste method is because when you use the .Value2 = .Value2 method, you are essentially iterating through every cell in the range and reassigning its value. This can be computationally expensive when dealing with large ranges.

The copy and paste method, on the other hand, is a single operation that is performed on the entire range at once. This can be faster, especially when working with large ranges.

Another approach you could try is to use the .Formula property instead of .Value2. This will allow you to hard code the formulas in the cells rather than the values. However, this approach may not be suitable for your use case depending on what you are trying to achieve.

You could also consider using Application.Calculation = xlCalculationManual before the operation, and Application.Calculation = xlCalculationAutomatic after the operation is done. This will prevent recalculation of the formulas on the worksheet during the operation.

You might also consider breaking the range into smaller chunks and doing the operation on each chunk one at a time.

Try These;

Sub HardCodeFormulas()
    Dim rg As Range
    Set rg = Range("Y18:EO8000") 'giant range of live formulas you want to hard-code

    Application.Calculation = xlCalculationManual 'prevent recalculation during the operation
    rg.Copy
    rg.PasteSpecial xlPasteValues
    Application.Calculation = xlCalculationAutomatic 'resume calculation after the operation is done
End Sub

Or

Sub HardCodeFormulas()
    Dim rg As Range
    Set rg = Range("Y18:EO8000") 'giant range of live formulas you want to hard-code

    Application.Calculation = xlCalculationManual 'prevent recalculation during the operation
    rg.Formula = rg.Formula
    Application.Calculation = xlCalculationAutomatic 'resume calculation after the operation is done
End Sub

 

[MS_Xsmell]

The reason you may be seeing better results using the copy and paste method is because when you use the .Value2 = .Value2 method, you are essentially iterating through every cell in the range and reassigning its value. This can be computationally expensive when dealing with large ranges.

The copy and paste method, on the other hand, is a single operation that is performed on the entire range at once. This can be faster, especially when working with large ranges.

Another approach you could try is to use the .Formula property instead of .Value2. This will allow you to hard code the formulas in the cells rather than the values. However, this approach may not be suitable for your use case depending on what you are trying to achieve.

You could also consider using Application.Calculation = xlCalculationManual before the operation, and Application.Calculation = xlCalculationAutomatic after the operation is done. This will prevent recalculation of the formulas on the worksheet during the operation.

You might also consider breaking the range into smaller chunks and doing the operation on each chunk one at a time.

Try These;

Sub HardCodeFormulas()
    Dim rg As Range
    Set rg = Range("Y18:EO8000") 'giant range of live formulas you want to hard-code

    Application.Calculation = xlCalculationManual 'prevent recalculation during the operation
    rg.Copy
    rg.PasteSpecial xlPasteValues
    Application.Calculation = xlCalculationAutomatic 'resume calculation after the operation is done
End Sub

Or

Sub HardCodeFormulas()
    Dim rg As Range
    Set rg = Range("Y18:EO8000") 'giant range of live formulas you want to hard-code

    Application.Calculation = xlCalculationManual 'prevent recalculation during the operation
    rg.Formula = rg.Formula
    Application.Calculation = xlCalculationAutomatic 'resume calculation after the operation is done
End Sub

Thanks. I have been benchmarking every possible combination of calculation toggle and value/value2/copy paste values and in this particular case, leaving calcs automatic and a simple copy/paste value is by far the fastest approach.

Surprising for sure.

Thanks for the reply. Leaving the thread open to see if anyone has anything additional to add to the conversation.