Introduction | Example | Tutorial | Applications | Comments
Introduction - Application.ScreenUpdating = False
Prevent your screen from updating until your Excel macro is finished with Application.ScreenUpdating=False. The Application.ScreenUpdating property is useful when running macros that jump from cell to cell, sheet to sheet, and workbook to workbook.
Setting the Application.ScreenUpdating to False stops your screen from flickering (refreshing) as your macro runs. It can be used for other applications, but I find it most useful for Excel.
Example - Application.ScreenUpdating = False
Screen Updating Demonstration
Sub ScreenUpdatingDemo() Application.ScreenUpdating = False Range("a1").Select For j = 1 To 10 For i = 1 To 25 Selection = i Selection.Offset(1, 0).Select Next i Selection.Offset(-25, 1).Select Next j Application.ScreenUpdating = True End Sub
Write better macros in half the time
Join thousands of others improving their VBA knowledge for free with our email series. Subscribe for more of our best VBA tips and access to our entire macro library.
Tutorial - Application.ScreenUpdating = False
This demo uses Selection.Offset to forcefully change the selected cell in Excel. I recommend disabling ScreenUpdating anytime you are manually selecting cells or worksheets. Play around with the
Application.ScreenUpdating property to prove it works.
Displaying each step slows your macro down. It’s a good practice to disable screen updating to decrease total run time. Don’t forget to set your ScreenUpdating property back to True when you’re done.
Application Ideas - Application.ScreenUpdating = False
The silly code I presented in the ScreenUpdating Demonstration just numbers cells. Better uses for Application.ScreenUpdating=False are presented below:
- Adding new Worksheets
- Manipulating Word Documents from Excel
- Sending email from Excel
- Switching between Excel Sheets.
- Opening new Excel Workbooks.
- Switching between pages in Word.
Leave a comment below and let me know how you plan on using Application.ScreenUpdating Property.