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
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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
Read More: [Fixed!] Excel Cells Not Updating Unless Double Click (5 Solutions)
Similar Readings
- How to Refresh Excel Sheet Automatically (3 Suitable Methods)
- How to Automatically Update a Pivot Table When Source Data Changes
- Pivot Table Not Refreshing (5 Issues & Solutions)
- How to Auto Refresh Pivot Table without VBA in Excel (3 Smart Methods)
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.
Read More: [Solved]: Excel Formulas Not Updating Until Save (6 Possible Solutions)
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.
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.
Related Articles
- How to Refresh Chart in Excel (2 Effective Ways)
- How to Disable Background Refresh in Excel (2 Handy Methods)
- VBA to Refresh Pivot Table in Excel (5 Examples)
- How to Refresh Pivot Table in Excel (4 Effective Ways)
- How to Refresh All Pivot Tables in Excel (3 Ways)
- How to Refresh All Pivot Tables with VBA (4 Ways)
