Disabling calculation of the worksheet can decrease running time of the macro significantly. Moreover, disabling events, screen updating and page breaks would be beneficial. Following
Sub can be used in any macro for this purpose.
Sub OptimizeVBA(isOn As Boolean) Application.Calculation = IIf(isOn, xlCalculationManual, xlCalculationAutomatic) Application.EnableEvents = Not(isOn) Application.ScreenUpdating = Not(isOn) ActiveSheet.DisplayPageBreaks = Not(isOn) End Sub
For optimization follow the below pseudo-code:
Sub MyCode() OptimizeVBA True 'Your code goes here OptimizeVBA False End Sub