Improve performance with VBA Selection.AutoFill

Question

I have code like the following:

Sub RMS()
    Application.Calculation = xlCalculationManual
    Sheets("m1").Range("A3").FormulaR1C1 = "=LEN(LEFT(m!R[2]C,FIND(""x"",m!R[2]C & "","")-1))"
    Range("A1:A3").Select
    Selection.AutoFill Destination:=Range("A1:EZ3"), Type:=xlFillDefault
    Range("A1:EZ3").Select
    Selection.AutoFill Destination:=Range("A1:EZ600"), Type:=xlFillDefault
    Range("A1:EZ600").Select
End Sub

This code is running very slow. Is there any help you can give so that code like this could run much faster because I run this code in multiple sheets?

starts by removing the `Select` and `Selection`, and modify the Ranges directly — Shai Rado, Feb 01 '17 at 07:52
If you search for anything on this topic you will always find a suggestion of turning off screen updating. Why don't you try searching google first — Nick.Mc, Feb 01 '17 at 07:53
Possible duplicate of [How to improve the speed of VBA macro code?](http://stackoverflow.com/questions/13016249/how-to-improve-the-speed-of-vba-macro-code) — Han Soalone, Feb 01 '17 at 07:57

score 6 · Accepted Answer · answered Feb 01 '17 at 07:58

This will be faster :

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

    With Sheets("m1")
        .Range("A3").FormulaR1C1 = "=LEN(LEFT(m!R[2]C,FIND(""x"",m!R[2]C & "","")-1))"
        .Range("A1:A3").AutoFill Destination:=.Range("A1:EZ3"), Type:=xlFillDefault
        .Range("A1:EZ3").AutoFill Destination:=.Range("A1:EZ600"), Type:=xlFillDefault
    End With

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With
End Sub

Thank you Sir, for taking time and helping me to find a solution. Its very helpfull — senget, Feb 01 '17 at 10:38

Improve performance with VBA Selection.AutoFill

1 Answers1