Apply VBA Screen Update OFF (Application.ScreenUpdating)

- Written by Puneet

What is VBA Screen Updating?

ScreenUpdating is a property in VBA that you can use to turn “ON” and “OFF” the screen updating while running the code. You can turn it off before running a code that makes your code run faster and then turn it on once the execution of the code is complete. You can read and write this property.

vba-screen-updating

By default, screen updating is “ON” in Excel. When you usually run a code, it takes a lot of flickering if that code takes time, but if you turn OFF the screen updating, it will take less time than usual to run.

Turn OFF Screen Updating in VBA

Here are the steps to write and turn off screen updating.

  1. First, type the keyword “Application”.
  2. After that, press a dot “.” to open the properties and methods list.
  3. Now, select “ScreenUpdating”.
  4. In the end, specify “False” to it.

Once you turn off screen updating, VBA will not turn it ON once the code is executed. So, it’s always better to turn it off from your end. The code would be like something below.

Points to Consider

  • Make sure to update the screen “ON” when using a user form.
  • If you are debugging code, it is better to have a screen that updates “ON” so you can see all the activities as they are.

Code Examples

Here are a few examples that you can check to learn more about using the screen update property in VBA in codes.

1. Copy Data

This macro disables screen updating for smoother performance, copies data from the range A1 in “Sheet1,” pastes only the values in “Sheet2” at cell A1, and then re-enables screen updating to restore normal functionality.

Sub CopyData()
' Disable screen updating
Application.ScreenUpdating = False

' Copy data from Sheet1 to Sheet2
Sheets("Sheet1").Range("A1:D1000").Copy
Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues

' Enable screen updating
Application.ScreenUpdating = True
End Sub
Sub DeleteRows()
'This macro disables screen updating, loops through rows 1000 to 1, deletes rows where column A contains "DeleteMe," and then re-enables screen updating for normal Excel display.
' Turn off screen updating
Application.ScreenUpdating = False

Dim i As Long
For i = 1000 To 1 Step -1
If Cells(i, 1).Value = "DeleteMe" Then
Rows(i).Delete
End If
Next i

' Turn screen updating back on
Application.ScreenUpdating = True
End Sub

Sub UpdateMultipleSheets()
'This macro disables screen updates, updates cell A1 in all worksheets to "Updated," and then re-enables screen updates for normal display.
' Disable screen updating
Application.ScreenUpdating = False

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Range("A1").Value = "Updated"
Next ws

' Enable screen updating
Application.ScreenUpdating = True
End Sub

Sub FormatCells()
' Turn off screen updating
Application.ScreenUpdating = False

Dim rng As Range
Set rng = Range("A1:A1000")

Dim cell As Range
For Each cell In rng
If cell.Value > 50 Then
cell.Font.Bold = True
cell.Interior.Color = RGB(255, 255, 0) ' Highlight with yellow
End If
Next cell

' Turn screen updating back on
Application.ScreenUpdating = True
End Sub

Sub GenerateReports()
' Disable screen updating
Application.ScreenUpdating = False

Dim i As Integer
For i = 1 To 5
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Report " & i
Sheets("Report " & i).Range("A1").Value = "Report #" & i
Next i

' Enable screen updating
Application.ScreenUpdating = True
End Sub