How to Stop Excel from Flickering during Macros

If you’ve ever written or recorded a macro in Excel, you’ve no doubt noticed that it shows everything on the screen you tell it to do. Depending on the code, Excel will show different cell locations or even different worksheets being selected during run time. Below I’m going to show you a line of VBA code that will not only eliminate those actions from being seen on the screen, but it will most likely increase the speed with which your macro runs.

Get a Macro Ready to Test
This post is more for those who are slightly familiar with writing or recording VBA macros in Excel.  If you don’t have a macro you want to test this on, you can always record a macro to see the feature.  If you record a macro, click around several times entering values, possibly changing spreadsheets and making some formatting changes.  If you run your macro as normal, it will show all of the actions on the screen.

ScreenUpdating
It’s easy to quickly see the effect this code will have on your macro.  There are 2 lines of code you will need to add.  At the beginning of your code add the line:

Application.ScreenUpdating = False

That’s it for the first part.  Then at the end of your code add the second line:

Application.ScreenUpdating = True

And that’s all there is to it.  You’re basically telling your macro not to update the screen or any actions to perform until it reaches the point in the code where you tell it to start showing updates again.  Like I said before, you should not only see a more pleasing update to your spreadsheet but you will most likely see an increase in speed for those longer running macros.

More Excel Tips

• Writing a Macro
• Macro to Disable Cell Calculations on a Sheet
• Is VBA in Excel 2007 Slower Than in Excel 2003?
• Using the Status Bar to Display Messages
• Count Rows with Data in a Macro