Suggestions for Macro Speed Improvement

[spectraflame]

Does anyone have any suggestions on how to improve the speed of this macro? The ELECTRIC_MXU_REPORT sheet contains about 9,400 rows. The rest of the Sheets referenced in the macro contain 10,000 rows or less.

Sub COMBINE_ALL_DATA()
Application.ScreenUpdating = False
MsgBox "This process could take up to 3 minutes to complete."
Sheets("ELECTRIC_MXU_REPORT").Select

Columns("G:G").Select
Selection.Delete Shift:=xlToLeft
Range("H1").Select
ActiveCell.FormulaR1C1 = "TYPE"
Range("I1").Select
ActiveCell.FormulaR1C1 = "SIZE"
Range("J1").Select
ActiveCell.FormulaR1C1 = "CLASS"
Range("K1").Select
ActiveCell.FormulaR1C1 = "SCALE"
Range("L1").Select
ActiveCell.FormulaR1C1 = "STATUS"
Range("M1").Select
ActiveCell.FormulaR1C1 = "TESTED"
Range("H1:M1").Select
Selection.Font.Bold = True
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("H2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC[-5],INCODE_METER_DATA,5,0)),0,VLOOKUP(RC[-5],INCODE_METER_DATA,5,0))"
Range("I2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC[-6],INCODE_METER_DATA,6,0)),0,VLOOKUP(RC[-6],INCODE_METER_DATA,6,0))"
Range("J2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC[-6],INCODE_MASTER_DATA,3,0)),0,VLOOKUP(RC[-6],INCODE_MASTER_DATA,3,0))"
Range("K2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC[-8],SCALE_FACTOR,5,0)),0,VLOOKUP(RC[-8],SCALE_FACTOR,5,0))"
Range("L2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC[-8],INCODE_MASTER_DATA,4,0)),0,VLOOKUP(RC[-8],INCODE_MASTER_DATA,4,0))"
Range("M2").Select
ActiveCell.FormulaR1C1 = _
"=IF(INDEX(ELECTRIC_METER_REPORT!C[-5],MATCH(ELECTRIC_MXU_REPORT!RC[-10],ELECTRIC_METER_REPORT!C[-11],0)+1)=0,"""",INDEX(ELECTRIC_METER_REPORT!C[-5],MATCH(ELECTRIC_MXU_REPORT!RC[-10],ELECTRIC_METER_REPORT!C[-11],0)+1))"
LR = ActiveSheet.UsedRange.Rows.Count
Range("H2").AutoFill Destination:=Range("H2:H" & LR)
Range("I2").AutoFill Destination:=Range("I2:I" & LR)
Range("J2").AutoFill Destination:=Range("J2:J" & LR)
Range("K2").AutoFill Destination:=Range("K2:K" & LR)
Range("L2").AutoFill Destination:=Range("L2:L" & LR)
Range("M2").AutoFill Destination:=Range("M2:M" & LR)
Columns("M:M").Select
Selection.NumberFormat = "mm/dd/yy"
Columns("H:M").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Range("H1:M1").Select
Columns("H:M").EntireColumn.AutoFit
Range("A2").Select
Application.ScreenUpdating = True
End Sub

 

[MrKowz]

Getting rid of all those Select/Selection statements will greatly improve performance:

Sub COMBINE_ALL_DATA()
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
MsgBox "This process could take up to 3 minutes to complete."
With Sheets("ELECTRIC_MXU_REPORT")
    
    .Columns("G:G").Delete Shift:=xlToLeft
    .Range("H1").Value = "TYPE"
    .Range("I1").Value = "SIZE"
    .Range("J1").Value = "CLASS"
    .Range("K1").Value = "SCALE"
    .Range("L1").Value = "STATUS"
    .Range("M1").Value = "TESTED"
    With .Range("H1:M1")
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    .Range("H2").FormulaR1C1 = _
        "=IF(ISNA(VLOOKUP(RC[-5],INCODE_METER_DATA,5,0)),0,VLOOKUP(RC[-5],INCODE_METER_DATA,5,0))"
    .Range("I2").FormulaR1C1 = _
        "=IF(ISNA(VLOOKUP(RC[-6],INCODE_METER_DATA,6,0)),0,VLOOKUP(RC[-6],INCODE_METER_DATA,6,0))"
    .Range("J2").FormulaR1C1 = _
        "=IF(ISNA(VLOOKUP(RC[-6],INCODE_MASTER_DATA,3,0)),0,VLOOKUP(RC[-6],INCODE_MASTER_DATA,3,0))"
    .Range("K2").FormulaR1C1 = _
        "=IF(ISNA(VLOOKUP(RC[-8],SCALE_FACTOR,5,0)),0,VLOOKUP(RC[-8],SCALE_FACTOR,5,0))"
    .Range("L2").FormulaR1C1 = _
        "=IF(ISNA(VLOOKUP(RC[-8],INCODE_MASTER_DATA,4,0)),0,VLOOKUP(RC[-8],INCODE_MASTER_DATA,4,0))"
    .Range("M2").SFormulaR1C1 = _
        "=IF(INDEX(ELECTRIC_METER_REPORT!C[-5],MATCH(ELECTRIC_MXU_REPORT!RC[-10],ELECTRIC_METER_REPORT!C[-11],0)+1)=0,"""",INDEX(ELECTRIC_METER_REPORT!C[-5],MATCH(ELECTRIC_MXU_REPORT!RC[-10],ELECTRIC_METER_REPORT!C[-11],0)+1))"
    LR = .UsedRange.Rows.Count
    .Range("H2:M2").AutoFill Destination:=.Range("H2:M" & LR)
    .Columns("M:M").NumberFormat = "mm/dd/yy"
    Application.Calculate
    With .Columns("H:M")
        .Copy
        .PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
    End With
    .Columns("H:M").EntireColumn.AutoFit
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
End With
End Sub

 

[JackDanIce]

Try:

Sub COMBINE_ALL_DATA()
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
 
MsgBox "This process could take up to 3 minutes to complete."
Sheets("ELECTRIC_MXU_REPORT").Select
Columns("G:G").Delete Shift:=xlToLeft
Range("H1").FormulaR1C1 = "TYPE"
Range("I1").FormulaR1C1 = "SIZE"
Range("J1").FormulaR1C1 = "CLASS"
Range("K1").FormulaR1C1 = "SCALE"
Range("L1").FormulaR1C1 = "STATUS"
Range("M1").FormulaR1C1 = "TESTED"
 
With Range("H1:M1")
    .Font.Bold = True
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .Orientation = 0
End With
Range("H2").FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC[-5],INCODE_METER_DATA,5,0)),0,VLOOKUP(RC[-5],INCODE_METER_DATA,5,0))"
Range("I2").FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC[-6],INCODE_METER_DATA,6,0)),0,VLOOKUP(RC[-6],INCODE_METER_DATA,6,0))"
Range("J2").FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC[-6],INCODE_MASTER_DATA,3,0)),0,VLOOKUP(RC[-6],INCODE_MASTER_DATA,3,0))"
Range("K2").FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC[-8],SCALE_FACTOR,5,0)),0,VLOOKUP(RC[-8],SCALE_FACTOR,5,0))"
Range("L2").FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC[-8],INCODE_MASTER_DATA,4,0)),0,VLOOKUP(RC[-8],INCODE_MASTER_DATA,4,0))"
Range("M2").FormulaR1C1 = _
"=IF(INDEX(ELECTRIC_METER_REPORT!C[-5],MATCH(ELECTRIC_MXU_REPORT!RC[-10],ELECTRIC_METER_REPORT!C[-11],0)+1)=0,"""",INDEX(ELECTRIC_METER_REPORT!C[-5],MATCH(ELECTRIC_MXU_REPORT!RC[-10],ELECTRIC_METER_REPORT!C[-11],0)+1))"
LR = ActiveSheet.UsedRange.Rows.Count
Range("H2:M2").Resize(LR).FillDown
Columns("M:M").NumberFormat = "mm/dd/yy"
 
With Columns("H:M")
    .Copy
    .PasteSpecial Paste:=xlValues
End With
 
Columns("H:M").EntireColumn.AutoFit
Range("A2").Select
With Application
    .CutCopyMode = False
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
End With
 
End Sub

 

[spectraflame]

I really appreciate your suggestions. Making a few changes has shaved off an entire minute.

Thanks again,
Matthew