Speeding up a macro

[BDD2015]

I recorded and then modified a macro. It seems fairly easy but it takes like 20 or 30 minutes to run. It puts a formula in columns, autofills the columns and then copies, paste special values them all. I am wondering if there is a way to make it run faster. There are many columns with formulas that go through the process so i have to wonder if it's just too big and unavoidable.

I also don't really understand the process. In File - Options, Enable Multi Threaded Calculations and Use All Processors (4) is checked. Enable Iterative Calculation is unchecked and I have no idea what that means.

As the macro runs it shows that all processors are being used and works its way from 0% to 100%. It repeats the 0-100% part multiple times. I don't understand that. Why not just once? Why does it restart the calculations multiple times?

I am attaching the code in a workbook. It has no data or anything - just the code. Is it possible for anyone to look at the code and maybe suggest a way to make it run faster? If necessary I can attach a complete sample so the code can be run but I don't know if it's necessary.

Anything anyone can suggest to make it faster would be so, so appreciated by me.

[jeffreybrown]

One quick suggestion:

You very rarely ever have to select an object to work with it...

http://www.tushar-mehta.com/excel/vb...rder/index.htm

Example:

Range("C2").Select
Selection.EntireColumn.Insert

Becomes:

Range("C2").EntireColumn.Insert

You can also research...

https://www.soa.org/News-and-Publica...s42-roper.aspx

or

https://www.techrepublic.com/blog/mi...-excel-macros/

or

http://datapigtechnologies.com/blog/...xcel-vba-code/


Also, it might be easier to get help if you post a small sample workbook with the code in the VBE.

[BDD2015]

Hi, Jeff

Thank you. I will make your Select change and check out the links you provided. I also just noticed that the range for the formula doesn't match the corresponding range for the autofill. I added a column a day or two ago. Fixed formulas, not autofill ranges. Although even before I messed that part up and it was running properly, it took forever. I will however fix the ranges, make your change and see what happens. And I'll check out your links. This Excel beast is such a struggle... But thoroughly enjoyable and addictive. The challenge... The conquer... But, oh so many pieces in the puzzle... Sigh. Let's see if I'm back here tomorrow night with a sample workbook. lol. Thanks again.

[leelnich]

Here's the code with all the Selects removed. Other changes are Highlighted in RED. I was unable to test it due to presence of un-available User Defined Functions and Named Ranges:

Sub BDD_3()
    
    Application.Calculation = xlCalculationManual
    Sheets("PPOSE").Select
    Range("G2:G13412").FormulaR1C1 = "=VLOOKUP(RC[50],Unique,4,FALSE)"
    Range("A2:A13412").FormulaR1C1 = "=N(COUNTIFS(R2C[1]:RC[1],RC[1])=1)"
    Range("AJ2:AJ13412").FormulaR1C1 = "=VLOOKUP(JOB_KEY_PPOSE,Unique,5,FALSE)"
    Range("AY2:AY13412").FormulaR1C1 = "=IF(SUM(IFERROR(SEARCH(""MANAGER"",JOB_KEY_PPOSE),))>0,""MGR"","""")"
    Range("AA2:AA13412").FormulaR1C1 = "=IF(SUM(IFERROR(SEARCH(""MAIN"",AREA_PPOSE),))>0,""MAIN"","""")"
    Range("AB2:AB13412").FormulaR1C1 = "=IF(SUM(IFERROR(SEARCH(""PCR"",AREA_PPOSE),))>0,""PCR"","""")"
    Range("AC2:AC13412").FormulaR1C1 = "=VLOOKUP(JOB_KEY_PPOSE,Unique,6,FALSE)"
    Range("AD2:AD13412").FormulaR1C1 = "=BDConcat(RC[35]:RC[36])"
    Range("AE2:AE13412").FormulaR1C1 = "=IF(SUM(IFERROR(SEARCH(""ON CALL"",JOB_KEY_PPOSE),))>0,""ON CALL"","""")"
    Range("AF2:AF13412").FormulaR1C1 = "=IF(SUM(IFERROR(SEARCH(""TEMP"",JOB_KEY_PPOSE),))>0,""TEMP"","""")"
    Range("AG2:AG13412").FormulaR1C1 = "=IF(SUM(IFERROR(SEARCH(""CHRISTMAS"",JOB_KEY_PPOSE),))>0,""XMAS"","""")"
    Range("AH2:AH13412").FormulaR1C1 = "=BDConcat(RC[9]:RC[11])"
    Range("AI2:AI13412").FormulaR1C1 = "=IF(SUM(IFERROR(SEARCH(""RVU"",JOB_KEY_PPOSE),))>0,""RVU"","""")"
    Range("AQ2:AQ13412").FormulaR1C1 = "=IF(SUM(IFERROR(SEARCH(""PART"",JOB_KEY_PPOSE),))>0,""PT"","""")"
    Range("AR2:AR13412").FormulaR1C1 = "=IF(OR(ISNUMBER(SEARCH({"" PT"","")PT""}, JOB_KEY_PPOSE))), ""PT"", """")"
    Range("AS2:AS13412").FormulaR1C1 = "=IF((SUM(IFERROR(SEARCH(""P/T"",JOB_KEY_PPOSE),0)))>0,""PT"","""")"
    Range("AT2:AT13412").FormulaR1C1 = "=IF(AND(RC[-45]=1,COUNTIFS(C[-44],RC[-44],C[-40],""GM"")>0),""GM"","""")"
    Range("AU2:AU13412").FormulaR1C1 = "=IF(AND(RC[-46]=1,COUNTIFS(C[-45],RC[-45],C[-39],""DIR"")>0),""DIR"","""")"
    Range("AV2:AV13412").FormulaR1C1 = "=IF(AND(RC[-47]=1,COUNTIFS(C[-46],RC[-46],C[-38],""MGR"")>0),""MGR"","""")"
    Range("AW2:AW13412").FormulaR1C1 = "=IF(AND(RC[-48]=1,COUNTIFS(C[-47],RC[-47],C[-37],""SPT"")>0),""SPT"","""")"
    Range("AX2:AX13412").FormulaR1C1 = "=IF(AND(RC[-49]=1,COUNTIFS(C[-48],RC[-48],C[-36],""SPV"")>0),""SPV"","""")"
    Range("AZ2:AZ13412").FormulaR1C1 = "=IF(SUM(IFERROR(SEARCH(""MGR"",JOB_KEY_PPOSE),))>0,""MGR"","""")"
    Range("BA2:BA13412").FormulaR1C1 = "=IF(SUM(IFERROR(SEARCH(""SPT"",JOB_KEY_PPOSE),))>0,""SPT"","""")"
    Range("BB2:BB13412").FormulaR1C1 = "=IF(SUM(IFERROR(SEARCH(""SUPERINTENDENT"",JOB_KEY_PPOSE),))>0,""SPT"","""")"
    Range("BC2:BC13412").FormulaR1C1 = "=IF(SUM(IFERROR(SEARCH(""STF"",AREA_PPOSE),))>0,""STF"","""")"
    Range("BD2:BD13412").FormulaR1C1 = "=IF(SUM(IFERROR(SEARCH(""EOD"",AREA_PPOSE),))>0,""EOD"","""")"
    Range("BF2:BF13412").FormulaR1C1 = "=BDConcat(RC[1]:RC[2])"
    Range("BG2:BG13412").FormulaR1C1 = "=IF(SUM(IFERROR(SEARCH(""LETTER CARRIER"",JOB_KEY_PPOSE),))>0,""LTR CAR"","""")"
    Range("BH2:BH13412").FormulaR1C1 = "=IF(SUM(IFERROR(SEARCH(""LC"",JOB_KEY_PPOSE),))>0,""LTR CAR"","""")"
    Range("BI2:BI13412").FormulaR1C1 = "=IF(AND(SPV="""",RC[-3]=""LTR CAR""),""LTR CAR"","""")"
    Range("BJ2:BJ13412").FormulaR1C1 = "=IF(SUM(IFERROR(SEARCH(""RETAIL"",JOB_KEY_PPOSE),))>0,""RETAIL"","""")"
    Range("BK2:BK13412").FormulaR1C1 = "=IF(SUM(IFERROR(SEARCH(""CSC"",AREA_PPOSE),))>0,""RETAIL"","""")"
    Range("BL2:BL13412").FormulaR1C1 = "=IF(SUM(IFERROR(SEARCH(""RELIEF"",JOB_KEY_PPOSE),))>0,""RELIEF"","""")"
    Range("BM2:BM13412").FormulaR1C1 = "=IF(AND(SPT="""",RC[-1]=""RELIEF""),""RELIEF"","""")"
    Range("BN2:BN13412").FormulaR1C1 = "=IF(SPV="""",RC[-2],"""")&"""""
    Range("C2:C13412").FormulaR1C1 = "=BDConcat(RC[43]:RC[47])"
    Range("D2:D13412").FormulaR1C1 = "=IF((SUM(IFERROR(SEARCH(""*ROLL"",JOB_KEY_PPOSE),0)))>0,""ROLLUP"","""")"
    Range("F2:F13412").FormulaR1C1 = "=IF(SUM(IFERROR(SEARCH(""GM"",JOB_KEY_PPOSE),))>0,""GM"","""")"
    Range("H2:H13412").FormulaR1C1 = "=IF(SUM(IFERROR(SEARCH(""DIR"",JOB_KEY_PPOSE),))>0,""DIR"","""")"
    Range("I2:I13412").FormulaR1C1 = "=IF(DIR=""DIR"",AREA_ORG,"""")"
    Range("J2:J13412").FormulaR1C1 = "=BDConcat(RC[41]:RC[42])"
    Range("K2:K13412").FormulaR1C1 = "=IF(MGR=""MGR"",VLOOKUP(ORG,DIRMAN,3,FALSE),"""")"
    Range("L2:L13412").FormulaR1C1 = "=BDConcat(RC[41]:RC[42])"
    Range("M2:M13412").FormulaR1C1 = "=IF(SPT=""SPT"",AREA_ORG,"""")"
    Range("N2:N13412").FormulaR1C1 = "=IF(SUM(IFERROR(SEARCH(""SPV"",JOB_KEY_PPOSE),))>0,""SPV"","""")"
    Range("O2:O13412").FormulaR1C1 = "=IF(SPV=""SPV"",AREA_ORG,"""")"
    Range("P2:P13412").FormulaR1C1 = "=IF(AND(SPV=""SPV"",RC[48]=""RELIEF""),""RELIEF"","""")"
    Range("Q2:Q13412").FormulaR1C1 = "=IF(AND(SPV=""SPV"",RC[41]=""LTR CAR""),""LTR CAR"","""")"
    Range("R2:R13412").FormulaR1C1 = "=IF(SUM(IFERROR(SEARCH(""PEAK"",JOB_KEY_PPOSE),))>0,""PEAK"","""")"
    Range("S2:S13412").FormulaR1C1 = "=IF(AND(SPV=""SPV"",RC[36]=""STF""),""STF"","""")"
    Range("T2:T13412").FormulaR1C1 = "=IF(AND(SPV=""SPV"",RC[36]=""EOD""),""EOD"","""")"
    Range("U2:U13412").FormulaR1C1 = "=BDConcat(RC[41]:RC[42])"
    Range("V2:V13412").FormulaR1C1 = "=IF(SUM(IFERROR(SEARCH(""OPS"",AREA_PPOSE),))>0,""OPS"","""")"
    Range("W2:W13412").FormulaR1C1 = "=IF(ISNUMBER(SEARCH("" LA "","" ""&AREA_PPOSE&"" "")),""LA"","""")"
    Range("X2:X13412").FormulaR1C1 = "=IF(SUM(IFERROR(SEARCH("" MP"",AREA_PPOSE),))>0,""MP"","""")"
    Range("Y2:Y13412").FormulaR1C1 = "=IF(SUM(IFERROR(SEARCH(""STN MAIN"",AREA_PPOSE),))>0,""STN MAIN"","""")"
    Range("Z2:Z13412").FormulaR1C1 = "=IF(SUM(IFERROR(SEARCH(""LCD"",AREA_PPOSE),))>0,""LCD"","""")"
    Application.Calculation = xlCalculationAutomatic
    
    With Range("A4:BO4")
    .Value = .Value
    End With
    
    Range("C2").EntireColumn.Insert
    Range("C1") = "DIR AREA"
    With Range("C2:C13412")
        .FormulaR1C1 = "=VLOOKUP(RC[-1],DIRMAN[#All],2,FALSE)"
        .Value = .Value
        .EntireColumn.ColumnWidth = 16.14
    End With
    
End Sub

Note that I substituted Values for Formulas directly (no copy/paste), and did so BEFORE inserting the column (which will force a recalculation.)