How to Stop Excel from Flickering during Macros
- 12 Comment
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
12 Comments on this post
Trackbacks
-
Blayne said:
What a great tip! Thanks for sharing.
March 15th, 2010 at 9:59 am -
Automobile India said:
I know you have to add some lines of code in the Visual Basic Editor, but I can’t remember what exactly to type. My macro is just copying and pasting stuff and flickers when I run it…
June 1st, 2010 at 11:55 pm -
Automobile India said:
If you record a macro, click around several times entering values, possibly changing spreadsheets and making some formatting changes…
June 1st, 2010 at 11:57 pm -
Jesse said:
Awesome, this helped me out alot!
August 1st, 2010 at 1:39 am -
apartments cannes said:
I know you need to add some lines of code inside Visual Standard Editor, but I can’t remember what precisely to form. My macro is just copying and pasting stuff and flickers when I operate it…
August 8th, 2010 at 12:44 pm -
apartments cannes said:
I know you have to add some lines of code from the Visual Standard Editor, but I cannot bear in mind what specifically to type. My macro is just copying and pasting stuff and flickers when I operate it…
August 8th, 2010 at 12:45 pm -
cannes rental said:
Should you record a macro, click close to many times entering values, possibly altering spreadsheets and creating some formatting changes
August 8th, 2010 at 12:46 pm -
Generators said:
I realize you have to add some lines of code from the Visual Common Editor, but I can’t keep in mind what specifically to variety. My macro is just copying and pasting stuff and flickers when I operate it…
September 4th, 2010 at 4:47 am -
Certified Translation said:
Must you record a macro, click close to many times getting into values, possibly altering spreadsheets and producing some formatting alterations
December 19th, 2010 at 11:55 am -
Keith said:
WOW! tried this, and runtime went down from about 25 seconds to about 5!!!!
March 7th, 2011 at 11:44 am -
charles said:
hi i need help with vbasic on excel
May 11th, 2011 at 11:21 am -
Dave said:
I enjoy your website, and reading all the helpful hints and tricks, but you REALLY need to turn off the Popup that wants me to sign up for a news letter I already receive. Please email me when I can begin using your website again without this annoyance.
Thanks!
July 26th, 2011 at 12:17 pm
