Screen Updating = True End Sub Thanks for your comments. Visible '(any other code in here) 'Don't forget to restore previous settings Application. Calculation = xl Automatic I hope it works not just for me, but for anyone who gives it a try. (PS.: I hope the format for this answer respects the rules. Close Save Changes:=True End If Next r Cell Application. And yes, the status bar is just a nice to have, but it at least provides the user with some kind of progress report, otherwise you don't know if the macro is still working or not. Without that, it will hit an error and not close the remaining open books.I want to start off this post by thanking everyone who sent in their examples in response to my January request.
As a result, of all this selecting and scrolling, the screen flickers whenever your recorded macro is played back. Screen Updating Those that are familiar with VBA code may also be aware of the term Application. Unfortunately those that really need to know about this term (those that can only record macros) are often not aware of it.
Just one recalculation and one redraw at the end of your code execution is enough to get the workbook current with all your changes. Enable Events display Page Break State = Active Sheet. Since it requires lots of resources to draw the screen so frequently, just turn off drawing the screen until the end of your code execution.
Here’s some sample code that shows how and what to shut off while your code runs. Display Page Breaks ‘note this is a sheet-level setting ‘turn off some Excel functionality so your code runs faster Application. Be sure to turn it back on right before your code ends.
There are tons of sites, pages, and people who are experts as well on this subject, have performed their own tests, and shared their results and ideas.
If you think I missed an important concept for how to optimize Excel VBA performance, or if you’ve got a valuable comment or link to share, please feel free to post here so everyone can benefit. Turn Off Everything But the Essentials While Your Code is Running This optimization explicitly turns off Excel functionality you don’t need to happen (over and over and over) while your code runs.