Mar 13 2010

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.

[ad#in-post-ad]

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

Did you find this post helpful? Try signing up for our free daily tips.

Enter your email address: 

          

16 Comments on this post

Trackbacks

  1. Blayne said:

    What a great tip! Thanks for sharing.

    March 15th, 2010 at 9:59 am
  2. 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
  3. 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
  4. Jesse said:

    Awesome, this helped me out alot!

    August 1st, 2010 at 1:39 am
  5. 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
  6. 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
  7. 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
  8. 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
  9. 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
  10. Keith said:

    WOW! tried this, and runtime went down from about 25 seconds to about 5!!!!

    March 7th, 2011 at 11:44 am
  11. charles said:

    hi i need help with vbasic on excel

    May 11th, 2011 at 11:21 am
  12. 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
  13. Saw said:

    I love this Macro. Simple but powerful. Quietly running the codes behind.

    May 23rd, 2012 at 6:08 am
  14. Saw said:

    @Charles: You are already here to learn what you really want.

    May 23rd, 2012 at 6:09 am
  15. Ryan said:

    Hi there,
    Is the a macro code that can recognise a picture locked into a cell, then delete when executed. My macro currently is able to move/copy/cut the pictures where the cells are defined, but when the code executes a ‘delete’ cell function, only the contents of the cell is deleted, leaving the picture on the cell therefore I have to manually delete the pictures.
    thanks in advance.

    October 15th, 2012 at 12:37 am
  16. ade said:

    I am using the following code to automatically assign the value in cell C6 to the tab (sheet) name. It works well, except that when I select any other cell, the sheet flickers a little. What do you suggest to remove the flicker?

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Range(“C6″) = “” Then
    ActiveSheet.Name = “FirstName LastName”
    Else
    ActiveSheet.Name = Range(“C6″).Value
    End If
    End Sub

    November 12th, 2013 at 4:16 pm

LEAVE A COMMENT

Subscribe Form

Subscribe to Blog

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks

Categories

Archives