How to Make Macros Run A LOT Faster

Add to Favorites
Author:

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 :)


Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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 ...
Put Data into a UserForm
Tutorial: How to take data from Excel and put it into a UserForm. This is useful when you use a form...
5 Best Tips for Tables in Excel (To Make You a Pro!)
Tutorial: Speed up your workday with these 5 great Table Tips for Excel! These tips make it easier ...
Tutorial Details
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course