|There are a number of ways to speed up a large spreadsheet. Some of them are:|
Turn off screen updating
To turn off screen updating use:
Note that you may want to save the current value and then restore it which is a better practice.
Turn off recalculation
You can speed up Excel VBA Code by turning Recalculation off and on
Application.Recalculation = False
Application.Recalculation = True
Application.EnableEvents = False
Optimize the code
Using Ctrl+Break find out where the code is spending most of the time and optimize it.
You can use:
Reduce the amount of data
Are you putting too much information in the spreadsheet? If spreadsheet is too large who will be able to use the information?
Convert Formulas to values
Copy your fomulas to the clipboard and then Paste Values to turn them to values. This will often help when slow Vlookups are taking place.
Use native Excel over VBA
Use Excel function of VBA developed functions they're much faster.
Use a tool to reduce the spreadsheet size
Use software to identify what's using up too much space. I have a tool called Determine Sheet Size that can help with this that I'll publish info about in the near future or contact us at
Move Excel VBA code to SQL
If your processing data in a sheet that was retrieved from a database, see if some of the work can be done in the database.