This follows on from How to Speed Up a Macro – Part 1
This week’s problem was a huge Excel file (20MB – not mine BTW!) that contains lots of data and 2 small macros. The first macro runs through several thousand rows of data, deleting rows that match a given criteria. The second macro takes the remaining data, performs some calculations and sorts it.
Both macros run very very slowly and with the requirement of a same-day solution (customers, eh!) I had no time to re-write the macros so I added 4 lines of code to each macro.
As well as adding Application.Screenupdating = False to the macro (see How to Speed Up a Macro – Part 1 for more details), I noticed that the message “Calculating (2 Processors)” and a percentage amount appeared in Excel’s Status Bar.
By default, Excel runs in Automatic Calculation mode. In other words, when you update a cell, any formulas based on that cell automatically update. In a huge spreadsheet this can cause Excel to run very slowly.
A quick fix is to put Excel into Manual Calculation mode. This means that when you update cells, Excel doesn’t recalculate anything, but you need to remember to press F9 (the Recalc key). This updates any cells that need updating at a time that suits you.
I say it’s a quick fix because really there’s a more serious problem – the spreadsheet has not been set up in the most efficient way and will probably need some major surgery and TLC!
Rather than having to remember to manually switch to Manual Recalculation mode and then switch Excel back to Automatic Recalculation mode, simply add the following line at or near the top of the macro:
Application.Calculation = xlManual
To automate the switching back to Automatic Calculation mode, add the following line at the appropriate place in the macro (at or near the end):
Application.Calculation = xlAutomatic
