Is there a smarter way to do this: 1-Copy Formula >> 2-Paste Formula Down >> 3-Hardcode Pasted Range process I use frequently?

D

d0rian

Active Member
Joined
May 30, 2015
Messages
313
Office Version
  1. 365
I have a 30-MB file (8 sheets, some with 80,000 rows) I try to keep as trim and nimble as possible, but it's gotten really laggy and crashes frequently now. I'm rebuilding it from scratch and hoping to discover ways to improve some processes that perhaps I've been doing a dumb/slow way. The one I use most often is a copy/paste-formulas/copy/paste-values process, because with 80,000+ rows, I try to keep as much of the sheet hardcoded wherever possible. So a typical snippet of VBA looks like this:

VBA Code: 
Sub CPHC()
    Application.ScreenUpdating = False
    With Sheets("cad")
        .Range("formula_source").Copy
        .Range(Range("pasterange.C").Value).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        .Range(Range("pasterange.C").Value).Copy
        .Range(Range("pasterange.C").Value).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    End With
    Application.ScreenUpdating = True
    Application.CutCopyMode = False
End Sub
Hopefully it's self-explanatory, but it:
  1. Copies a range (e.g. "formula_source" = D1:N1) that has the formula I want to paste to 80,000 rows
  2. Pastes the formula to the desired range I need to run calculations on (e.g. D2:N80000)
  3. Then Copies/Pastes that target range (D2:N80000) so that those values are hardcoded, not live formulas (otherwise, I'd have 80,000 formulas recalculating all the time, and the file would be unusable.)
Depending on the formulas I'm pasting down, the above can take 5-30 seconds, and I want to know whether there's a much speedier way to be doing this sort of process. Ideas I've had (which I'm not sure exist):

1. Is there any Excel function that -- in a single step -- will paste the hardcoded RESULTS of formulas, rather than the live formula itself? Theoretically that would eliminate step #3 above, and I wouldn't have to copy the target D2:N80000 range and paste-values. I don't think so, but if there is, that might cut this process in half.

2. Is there a smart way to instead accomplish the above with VBA where...like, all the calculations are done "in memory" or something? My code above essentially just automates the copy/paste-down/hardcode-values process (with screen-updating turned off), but can I somehow use VBA to, I dunno, make all of those calculations happen in the background rather than in the Sheet cells themselves, and just have the RESULTS pasted in the target range?

Open to any and all ideas!!
 
Sort by date Sort by votes
And I propose you test both of the following:

VBA Code: 
Sub FillDownFormulas3()                                                 ' Avg 1st of 5 = 3.996875 secs, 2nd of 5 = 4.04375 ... both avg = 4.0203125 secs
'
'-------------------------------------------------------------------
' Time Trial Beginning \/ \/
'
    Dim Counter As Long
    Dim TimedTrials As Long
'
    Range("J1:P1") = Array("Time Trial 1", "Time Trial 2", "Time Trial 3", "Time Trial 4", "Time Trial 5", , "Average Time")
    Range("J1:P1").Font.Bold = True
'
    For TimedTrials = 1 To 5
        Start = Timer
'
'
'
'
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual                                           ' Turn off Auto calculation
'
    Dim LastRow_AW  As Long
    Dim LastRow_E   As Long
    Dim LastRow_D_N As Long
'
    LastRow_AW = 100000
    LastRow_E = 100000
'
    Range("AW2").AutoFill Destination:=Range("AW2:AW" & LastRow_AW), Type:=xlFillDefault    ' Fill down formula for this range
    Range("E2").AutoFill Destination:=Range("E2:E" & LastRow_E), Type:=xlFillDefault        ' Fill down formula for this range
'
    Application.Calculation = xlCalculationAutomatic                                        ' Turn Auto calculation back on
'
    Range("AW2:AW" & LastRow_AW).Value = Range("AW2:AW" & LastRow_AW).Value                 ' Remove formulas from this range, leave just the values
    Range("E2:E" & LastRow_E).Value = Range("E2:E" & LastRow_E).Value                       ' Remove formulas from this range, leave just the values
'
    Application.ScreenUpdating = True
'
'
'
'
'-------------------------------------------------------------------
' Time Trial Ending \/ \/
'
        Range("I2").Offset(0, TimedTrials) = Timer - Start
        TotalRunTime = TotalRunTime + Range("I2").Offset(0, TimedTrials)
    Next
'
    Range("P2") = TotalRunTime / 5
'
    MsgBox "Done Calculating"
End Sub





Sub test1()                                                         '  Avg 1st of 5 = 37.6765625 secs, 2nd of 5 = 37.25625 ... both avg = 37.46640625 secs
'
'-------------------------------------------------------------------
' Time Trial Beginning \/ \/
'
    Dim Counter As Long
    Dim TimedTrials As Long
'
'
    Range("J1:P1") = Array("Time Trial 1", "Time Trial 2", "Time Trial 3", "Time Trial 4", "Time Trial 5", , "Average Time")
    Range("J1:P1").Font.Bold = True
'
    For TimedTrials = 1 To 5
        Start = Timer
'
'
'
'
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    Range("AW2:AW100000").Formula = "=COUNTIF(A:A,""c^""&AV2&""*"")"
    Range("E2:E100000").Formula = "=INDEX(C:C,MATCH(D2,A:A,0))"
    Application.Calculation = xlCalculationAutomatic
    With Range("E2:E100000, AW2:AW100000")
        .Value = .Value
    End With
    Application.ScreenUpdating = True
'
'
'
'
'-------------------------------------------------------------------
' Time Trial Ending \/ \/
'
        Range("I2").Offset(0, TimedTrials) = Timer - Start
        TotalRunTime = TotalRunTime + Range("I2").Offset(0, TimedTrials)
    Next
'
    Range("P2") = TotalRunTime / 5
'
    MsgBox "Done Calculating"
End Sub

I believe they are both apples to apples. If I messed something up between the two, please let me know.
 
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
I prefer to use FastExcel by Charles Williams to time macro's which is much more accurate than the timer method.
I have no intention of doing endless time trials tonight.

The trial I did was a test on the simplest formula I could think of so it was a test purely of the Autofill method copying down a formula vs inserting the formula directly and the results line up with the results we have got previously when testing the methods.

If you believe autofill to be faster I have no issues with you using it.
 
Upvote 0
Duly noted.

I prefer to test the actual formulas being discussed in this thread. I am not sure why you referred to the tests that I proposed as 'endless time time trials' and shied away from them. They should run fairly quick according to your previous posts.
 
Upvote 0
johnnyL said:
s 'endless time time trials' and shied away from them. T
Click to expand...
Because I don't have the time tonight (it is 04:15 here) to get stuck testing again and again and so am not getting started with it.
I done a test on the methods (without anything else there to interfere with the methods) and got the result I got.
 
Upvote 0
Understood! 2 tests that take less than a couple minutes each is outside of your time frame right now. Maybe tomorrow will be a better time. No Worries!
 
Upvote 0
johnnyL said:
Understood! 2 tests that take less than a couple minutes each is outside of your time frame right now.
Click to expand...
E2 formula =INDEX(C:C,MATCH(D2,A:A,0))
AW2 formula =COUNTIF(A:A,"c^"&AV2&"*")

Results in the codes
VBA Code: 
Sub Macro1()
    Range("AW2").AutoFill Destination:=Range("AW2:AW" & 100000), Type:=xlFillDefault    ' Fill down formula for this range
    Range("E2").AutoFill Destination:=Range("E2:E" & 100000), Type:=xlFillDefault
    '4.06 seconds (best time 3 runs)
End Sub
Sub Macro2()
Range("AW2:AW" & 100000).Formula = "=COUNTIF(A:A,""c^""&AV2&""*"")"
Range("E2:E" & 100000).Formula = "=INDEX(C:C,MATCH(D2,A:A,0))"
'1.55 seconds (single run)
End Sub

**END**
 
Upvote 0
johnnyL said:
And I propose you test both of the following:
Click to expand...
Hello Johnny, unless I am mistaken your tests are invalid.
The only valid result is Time Trial 1 and this consistently has Mark's formula method running at least 2.2x faster.

Your repeat loops Time Trial 2 to 5 used in the average, run after the formulas have been replaced with Values which defeats the point of the exercise.

1636348571259.png
 
Upvote 0
Alex Blakenburg said:
Hello Johnny, unless I am mistaken your tests are invalid.
The only valid result is Time Trial 1 and this consistently has Mark's formula method running at least 2.2x faster.

Your repeat loops Time Trial 2 to 5 used in the average, run after the formulas have been replaced with Values which defeats the point of the exercise.
Click to expand...

See, that was what I was after, a look to see if apples were actually apples. I will fix that issue and repost. Thank you @Alex Blakenburg.
 
Upvote 0
New Tests:

VBA Code: 
Sub FillDownFormulas3_5()                                                 ' Avg 1st of 5 = 16.8 secs
'
'-------------------------------------------------------------------
' Time Trial Beginning \/ \/
'
    Dim Counter As Long
    Dim TimedTrials As Long
'
'
    Range("J1:P1") = Array("Time Trial 1", "Time Trial 2", "Time Trial 3", "Time Trial 4", "Time Trial 5", , "Average Time")
    Range("J1:P1").Font.Bold = True
'
    For TimedTrials = 1 To 5
        Start = Timer
'
'
'
'
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual                                           ' Turn off Auto calculation
'
    Dim LastRow_AW  As Long
    Dim LastRow_E   As Long
    Dim LastRow_D_N As Long
'
    LastRow_AW = 100000
    LastRow_E = 100000
'
    Range("AW2").Formula = "=COUNTIF(A:A,""c^""&AV2&""*"")"                                 ' Copy formula to be used
    Range("E2").Formula = "=INDEX(C:C,MATCH(D2,A:A,0))"                                     ' Copy formula to be used
'
    Range("AW2").AutoFill Destination:=Range("AW2:AW" & LastRow_AW), Type:=xlFillDefault    ' Fill down formula for this range
    Range("E2").AutoFill Destination:=Range("E2:E" & LastRow_E), Type:=xlFillDefault        ' Fill down formula for this range
'
    Application.Calculation = xlCalculationAutomatic                                        ' Turn Auto calculation back on
'
    Range("AW2:AW" & LastRow_AW).Value = Range("AW2:AW" & LastRow_AW).Value                 ' Remove formulas from this range, leave just the values
    Range("E2:E" & LastRow_E).Value = Range("E2:E" & LastRow_E).Value                       ' Remove formulas from this range, leave just the values
'
    Application.ScreenUpdating = True
'
'
'
'
'-------------------------------------------------------------------
' Time Trial Ending \/ \/
'
        Range("I2").Offset(0, TimedTrials) = Timer - Start
        TotalRunTime = TotalRunTime + Range("I2").Offset(0, TimedTrials)
    Next
'
    Range("P2") = TotalRunTime / 5
'
    MsgBox "Done Calculating"
End Sub





Sub test1()                                                         '  Avg 1st of 5 = 37.6765625 secs, 2nd of 5 = 37.25625 ... both avg = 37.46640625 secs
'
'-------------------------------------------------------------------
' Time Trial Beginning \/ \/
'
    Dim Counter As Long
    Dim TimedTrials As Long
'
'
    Range("J1:P1") = Array("Time Trial 1", "Time Trial 2", "Time Trial 3", "Time Trial 4", "Time Trial 5", , "Average Time")
    Range("J1:P1").Font.Bold = True
'
    For TimedTrials = 1 To 5
        Start = Timer
'
'
'
'
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    Range("AW2:AW100000").Formula = "=COUNTIF(A:A,""c^""&AV2&""*"")"
    Range("E2:E100000").Formula = "=INDEX(C:C,MATCH(D2,A:A,0))"
    Application.Calculation = xlCalculationAutomatic
    With Range("E2:E100000, AW2:AW100000")
        .Value = .Value
    End With
    Application.ScreenUpdating = True
'
'
'
'
'-------------------------------------------------------------------
' Time Trial Ending \/ \/
'
        Range("I2").Offset(0, TimedTrials) = Timer - Start
        TotalRunTime = TotalRunTime + Range("I2").Offset(0, TimedTrials)
    Next
'
    Range("P2") = TotalRunTime / 5
'
    MsgBox "Done Calculating"
End Sub

I fixed the issue for my version to add the two formulas each time the test is ran.

Please, when you people are testing the results, don't pick code from one post to test against code from another post. Please test the codes from this post. I want to compare apples to apples. If you see an issue with the tests from this post, please say so and I will correct them as needed.
 
Upvote 0
...,and this is what I meant by "endless time trials". Thanks @Alex Blakenburg for your test results.
Hopefully the thread will get back to the OP's original problem now.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Copy /past cells by inputbox on sheet 2 to sheet1 using VBA
2
Replies
12
Views
644
DanteAmor
DanteAmor
I
  • Question Question
Filter column on text value if that value exists using VBA
Replies
7
Views
411
Peter_SSs
Peter_SSs
M
  • Solved
Have specified copy range using Range("I6", ActiveCell...but get an error when pasting
Replies
6
Views
245
jolivanes
jolivanes
H
  • Question Question
VBA Speed up Copy and paste
Replies
3
Views
677
skillilea
S
L
  • Solved
VBA Copy and Paste Macro doesnt line up correctly
2
Replies
11
Views
496
Alex Blakenburg
A

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top