ScreenUpdating, Calculation & Events: Speed Up Your Macros Tenfold

Control Excel’s global settings to avoid unnecessary work.

Template

Sub WithToggles()
    Dim oldCalc As XlCalculation, oldEvents As Boolean, oldSU As Boolean
    oldCalc = Application.Calculation
    oldEvents = Application.EnableEvents
    oldSU = Application.ScreenUpdating

    On Error GoTo CleanUp
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    Application.ScreenUpdating = False

    ' ... heavy operations ...

CleanUp:
    Application.Calculation = oldCalc
    Application.EnableEvents = oldEvents
    Application.ScreenUpdating = oldSU
End Sub

Always restore previous values in a CleanUp block.