Excel VBA: Turn Off Screen Update

It’s an important issue for all of us while working with VBA in Excel, to turn off the screen update. In this article, I’ll show you how you can turn off the screen update using VBA in Excel.

Excel VBA: Turn Off Screen Update (Quick View)

Sub Turn_Off_Screen_Update()

Application.ScreenUpdating = False

Count = 1
For i = 1 To 100
    For j = 1 To 100
        ActiveSheet.Cells(i, j) = Count
        Count = Count + 1
    Next j
Next i

Application.ScreenUpdating = True

End Sub

An Overview to Turn Off Screen Update Using Excel VBA

To turn off screen updating using VBA is easy. To tell the truth, only one line is enough to accomplish this.

Application.ScreenUpdating = False

This one line of code will turn off the screen updating for you, but you won’t be able to feel the effect with this one line. To feel it, insert some lines of code that will do some tasks for you after updating the screen. It’s better if it’s a long task, that will make you understand the effect of screen updating.

Count = 1
For i = 1 To 100
    For j = 1 To 100
        ActiveSheet.Cells(i, j) = Count
        Count = Count + 1
    Next j
Next i

These lines insert a series of 1 to 100,000 in the active sheet, starting from cell A1. If you do it without screen updating, it’ll take a long time. Because each time it inserts a number to the next cell, the earlier cell is updated along with.

But if you keep the screen updating off, the earlier cells won’t be updated each time, and the operation will take less time to be executed.

Then if you can wish, you can again turn on the screen update.

Application.ScreenUpdating = True

So the complete VBA code will be:

⧭ VBA Code:

Sub Turn_Off_Screen_Update()

Application.ScreenUpdating = False

Count = 1
For i = 1 To 100
    For j = 1 To 100
        ActiveSheet.Cells(i, j) = Count
        Count = Count + 1
    Next j
Next i

Application.ScreenUpdating = True

End Sub

Developing the Macro to Turn Off Screen Update Using Excel VBA

We’ve seen the step-by-step analysis of the code to turn off screen update using VBA in Excel. Now we’ll see how we can develop a Macro to execute this.

⧪ Step 1: Opening the VBA Window

Press ALT + F11 on your keyboard to open the Visual Basic window.

⧪ Step 2: Inserting a New Module

Go to Insert > Module in the toolbar. Click on Module. A new module called Module1 (or anything else depending on your past history) will open.

⧪ Step 3: Putting the VBA Code

This is the most important step. Insert the given VBA code in the module.

⧪ Step 4: Running the Code

Click on the Run Sub \ UserForm tool from the toolbar above.

The code will run. And you’ll find a series of numbers from 1 to 1,00,000 generated quickly on your worksheet, which would otherwise take a long time to be executed.

Things to Remember

You need not do the same task with me after turning off the screen update. You can do whatever is your regular task. But the thing is that you won’t understand the effect of turning screen updates off unless you do a long series of tasks. That’s why I generated a sequence from 1 to 1,00,000.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

Therefore, this is the process to develop a Macro to turn off screen updating using Excel VBA. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.