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

[d0rian]

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:

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!!

 

[offthelip]

It is very likely that it would be much faster to do the calculations all in VBA i.e "in memory" however you haven't stated what the formula in D1:N1 are. These would need to be implemented in VBA

 

[d0rian]

It is very likely that it would be much faster to do the calculations all in VBA i.e "in memory" however you haven't stated what the formula in D1:N1 are. These would need to be implemented in VBA

Ah, yes I suspected this might be the case -- i.e. that I'd have to insert into the VBA code the specific formulas/calculations I wanted done 'in memory'. OK, well here are 2 slow ones, if I could get some help on these, I'll see how much time this saves and decide whether it's worth learning how to convert cell-based formulas into their VBA equivalents. I've found that of all my formulas, the slowest ones to copy-down are COUNTIF or INDEX/MATCH functions.

FWIW, Column A has 60,000 rows.

AW2 has this formula: =COUNTIF(A:A,"c^"&AV2&"*")
- Just pasting the above formula (and then copy/pasting the results) to 3,000 cells in AW3:AW3000 takes 5-6 seconds.

E2 has this formula: =INDEX(C:C,MATCH(D2,A:A,0))
- This one takes 8+ seconds to paste down to 60,000 cells in E3:E60000

 

[johnnyL]

The following code to copy/paste those two formulas takes about 3 seconds to run:

Sub FillDownFormulas()
'
    Dim LastRow_AW  As Long
    Dim LastRow_E   As Long
'
    LastRow_AW = 3000
    LastRow_E = 60000
'
    Range("AW2").AutoFill Destination:=Range("AW2:AW" & LastRow_AW), Type:=xlFillDefault
    Range("E2").AutoFill Destination:=Range("E2:E" & LastRow_E), Type:=xlFillDefault
End Sub

 

[johnnyL]

The info provided thus far is contradictory. E2 formula was said to go to E60000, but then it was stated D1:N1 formulas go to 80000, thus overwriting the E column formulas.

That being said here is some code that writes the formulas and then leaves just the calculated values ie. no formulas. Takes about 20 seconds to run, 940000+ cells filled with formulas and then formulas removed leaving just the values. That's 1,886,000 cell changes.

Sub FillDownFormulas2()
'
    Application.ScreenUpdating = False
'
    Dim LastRow_AW  As Long
    Dim LastRow_E   As Long
    Dim LastRow_D_N As Long
'
    LastRow_AW = 3000
    LastRow_E = 60000
    LastRow_D_N = 80000
'
    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
    Range("D1:N1").AutoFill Destination:=Range("D1:N" & LastRow_D_N), Type:=xlFillDefault   ' This formula fill down will overwrite the previous line of code ;)
'
    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
    Range("D1:N" & LastRow_D_N).Value = Range("D1:N" & LastRow_D_N).Value                   ' Remove formulas from this range, leave just the values
'
    Application.ScreenUpdating = True
End Sub

 

[MARK858]

You can apply the formula directly to the cells in the range, it took 881.82 milliseconds on my laptop to insert.

Any other time issues are probably down to calculation which you can set to manual while the code is inserting.
With the .Value = .Value part it took 1.1 seconds (obviously with no other data on the sheet).

Sub test1()
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    Range("AW2:AW80000").Formula = "=COUNTIF(A:A,""c^""&AV2&""*"")"
    Range("E2:E60000").Formula = "=INDEX(C:C,MATCH(D2,A:A,0))"
    Application.Calculation = xlCalculationAutomatic
    With Range("E2:E60000, AW2:AW80000")
        .Value = .Value
    End With
    Application.ScreenUpdating = True

End Sub

 

[johnnyL]

I knew My code was taking too long, I forgot the auto calculation.

Code now takes about 5 seconds to run.

Sub FillDownFormulas3()
'
    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 = 3000
    LastRow_E = 60000
    LastRow_D_N = 80000
'
    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
    Range("D1:N1").AutoFill Destination:=Range("D1:N" & LastRow_D_N), Type:=xlFillDefault   ' This formula fill down will overwrite the previous line of code ;)
'
    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
    Range("D1:N" & LastRow_D_N).Value = Range("D1:N" & LastRow_D_N).Value                   ' Remove formulas from this range, leave just the values
'
    Application.ScreenUpdating = True
End Sub

My code takes longer than @MARK858's because it also includes the much larger portion of cell manipulation, the D1:N80000 portion.

 

[MARK858]

My code takes longer than @MARK858's because it also includes the much larger portion of cell manipulation, the D1:N80000 portion.

Also because autofill is slower than applying the formula directly

 

[johnnyL]

Also because autofill is slower than applying the formula directly

Actually, if you time test both methods, the autofill method I used is much faster than the method you used.

 

[MARK858]

@johnnyL
Put

=A1

in cell B1
run

Sub Afill()
Range("B1").AutoFill Range("B1:B100000")
End Sub

It takes 249.37 milliseconds to run for me
Run the below (doesn't need anything in B1)

Sub DIRECTLY()
Range("B1:B100000").Formula = "=A1"
End Sub

It takes 124.97 milliseconds for me

Methods tested