Speeding up Print macros

I have recorded a macro that changes print settings and then prints various sheets. The problem is that it takes a while to run. Is there a way to speed it up?

By Neale Blackwood

When you record macros Excel creates the VBA code for you. This is really useful and a great way to learn VBA. The downside is that the code it creates is not very efficient. When you record a macro to change print settings, the code records all the settings, whether they were changed or not. When you run the macro it goes through each of these settings and it is this process that takes the time. Basically you need to go through the code and delete the settings you didn’t change. This will speed up the macro.

As an example I recorded a macro to change the page orientation to landscape and turned on gridlines. The macro that Excel created had over 30 lines of code. I have amended it to the following macro which will run faster.

Sub ChangePrintSet()

With ActiveSheet.PageSetup

.PrintGridlines = True

.Orientation = xlLandscape

End With

End Sub

A useful command you can add to your macros to speed them up is:

Application.ScreenUpdating = False

This stops the screen flickering as the macro runs. You usually put it at the top of your macro code after the Sub line. You should also put:

Application.ScreenUpdating = True

at the end of your macro before the End Sub line.