How to Make Macros Run A LOT Faster

Add to Favorites

Here is a very simple and easy-to-use tip to make all of your Excel macros run A LOT faster.  It is very simple and all that is needed is to turn off "Screen Updating" in Excel.

This will allow your macros to run without having to update the Excel worksheet/workbook after each change the macro makes to that worksheet/workbook.  As such, a lot less computing power is required.

Here is the code below:

Private Sub YourMacro()

 ' Turn off screen updating
 Application.ScreenUpdating = False

  'All of your macro code goes here!

 'Turn screen updating back on
 Application.ScreenUpdating = True

End Sub

Application.ScreenUpdating is what controls whether the Excel worksheet/workbook will update after each time the macro affects the Excel workspace.  You simply set this to False before the start of your code in the macro and set it to True at the end of your maco.

Make sure to set this to True at the end though or Excel will not update when you make changes in the worksheet by hand too.  This is very important not to forget because you will not notice that this is turned-off at first and it may take you hours of trouble-shooting to find out why nothing changes when you change the data in a cell that is linked to other cells in the spreadsheet.

Note that this trick will not have any noticeable affect on macros that already run in the blink of an eye.  Where you really see the benefit of turning off ScreenUpdating is when you run a macro that will loop through many cells, rows, columns, sheets, etc. and perform some action on each of them.  In that and similar cases, you will notice a HUGE improvement in how fast your macro runs.

Hope this helps :)



Similar Content on TeachExcel
Run a Macro when a User Does Something in the Workbook in Excel
Tutorial: How to run a macro when a user does something within the Workbook in Excel, such as openi...
Automatically Run a Macro When a Workbook is Opened
Tutorial: How to make a macro run automatically after a workbook is opened and before anything els...
Automatically Run a Macro at a Certain Time - i.e. Run a Macro at 4:30PM every day
Macro: Automatically run an Excel macro at a certain time. This allows you to not have to worry a...
Run a Macro when a User Does Something in the Worksheet in Excel
Tutorial: How to run a macro when a user does something in the worksheet, everything from selecting ...
How to Create and Manage a Chart in Excel
Tutorial: In this tutorial I am going to introduce you to creating and managing charts in Excel. Bef...
Logical Comparison Operators in Excel - How to Compare Things
Tutorial: Logical comparison operators allow you to compare values in Excel to see if they are equal...