There are two commands you can insert at the top of your code to speed up your macros.

1. Screen Updating

The following command stops the screen from updating:

Application.ScreenUpdating = False

When creating a macro it can be useful (and fun) to see all the changes being made. You can make sure the macro does what you want. Once the macro has been tested and is ready to use, you can turn off screen updating with the above command. At the end of your code you should turn it back on again using the command:

Application.ScreenUpdating = True

2. Calculation

The second command that can speed up macros turns off the calculation.

Application.Calculation = xlManual

If your macro changes cell values that cause a recalculation, this can slow down the macro. Excel waits while each recalculation is done. If you have turned off calculation with the above command and you need to recalculate during the running of your macro then you can use the command:

Application.Calculate

Or

Application.CalculateFull

The second one works better in large Excel files that have huge numbers of calculations.

Again you should always turn calculation back on again at the end of your code with:

Application.Calculation = xlAutomatic

If your macro crashes and the last commands to reset screen updating and calculation are not executed then Excel will typically turn on screen updating, but you will need to manually reset the calculation back to automatic via the Formulas tab – see below.

Click here to see how you can run a Macro off a Quick Access Toolbar icon.