The following is some rather well known code that you can use to speed up your macros. It works by setting Calculation to Manual, running your code, then resetting Calculation to it's original status after the code has run. (Note ScreenUpdating being set to False, this keeps your screen "frozen" until the code is finished, this is a time saver too)
Dim CalSet as Long
CalSet = Application.Calculation
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
'Place your code here
Application.Calculation = CalSet
Application.ScreenUpdating = True
You can also change your Calculation settings by going to Tools, Options, Calculation. Switching to Manual Calculation can make working with large files a easier, but there are some very important things to keep in mind first.
As mentioned above, Manual Calculation can make things run a lot faster when inputting data, working with filters, even your macros depending on the code used and your original Calculation setting. (How your files are set up will have a big influence on how much time is saved)
Formulas don't work (well, that's not surprising), neither does Conditional Formatting (spotty at best), but you can tap the F9 key to calculate whenever you want to update (or Shift + F9 just to calculate the active worksheet).
I've looked in the Help files and this part doesn't seem to be mentioned. If you switch between Automatic and Manual, all open workbooks are "set" to that mode and will be saved accordingly. (You will be asked if you want to save changes when closing, but this might be overlooked if you lose track of which mode you are using)
Also be aware that the first workbook opened sets the Calculation mode for all subsequent workbooks. This means if the first workbook is set to Manual Calculation, all other workbooks opened later on will set to Manual by default. If you are careful, this may not be a problem, but consider the following scenarios. (Please correct me if I am incorrect or missing something)
1. You unwittingly save a Workbook in Manual Calculation. Several other workbooks might also be saved in this mode without you realizing what has happened and/or not knowing which workbooks were saved in this setting. (Don't forget you can always check by going to Tools, Options, Calculation if necessary)
2. A co-worker opens a workbook set in Manual Calculation or you email a workbook in that was saved Manual Calculation . This can cause problems because the user may fall into the same trap as above. Depending on their experience and /or whether they notice (assuming they actually know what Manual Calculation is), this might end up being a very big problem.
Use with due caution.
P.S. I use message boxes in some files with Workbook Open and Before Close events to verify Calculation settings and offer an option to change to either Automatic or Manual. But adding this to each and every file is not very practical :-(