There are two very simple ways to make your code run faster, and they are both properties of the Application object: ScreenUpdating and Calculation.
ScreenUpdating, set to False, causes your macro to run without refreshing the screen. This property can be useful if you don’t want the user to see the screen flicker as you write to cells or do other things. Even if you don’t care about flicker, refreshing the screen takes time and turning ScreenUpdating off allows Excel to devote its time to running you macro.
Calculation determines when Excel calculates as defined in Tools>Options>Calculation. If your code writes to cells, setting Calculation to xlCalculationManual can greatly reduce the execution time of your code. There are times when you need to calculate in your macro, such as if your macro uses Excel to get an intermediate calculation, but usually you can leave it set to manual until your done.
Don’t forget to set ScreenUpdating to True and Calculation back to xlCalculationAutomatic at the end of your code.
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic