On the eve before Christmas, I'm writing this article on VBA code optimization for my blog readers as a gift. For example, a macro that required several seconds to complete in an earlier version of Excel may require several minutes to complete in a later version of Excel.It contains both the knowledge and good practices which if followed and used effectively then it might put you in elite category of excellent VBA programmers. This problem may occur if the following conditions are true: * The VBA macro modifies the properties of many rows or columns.Note: As Jeff Weir pointed out in the comments below, this code will wipe out the Undo stack, so you won't be able to undo any steps you've previously taken. Column Width Merge Width = 0 For Each c M In Auto Fit Rng c M. Column Width Merge Width Next 'small adjustment to temporary width Merge Width = Merge Width Auto Fit Rng. So, instead of using the Worksheet_Change event, you could use the workbook's Before Print event, to reduce the Undo problem.
You may turn off the automatic calculation using Application.
You must set the Screen Updating property to True when the procedure finishes or when it stops after an error. Turn off automatic calculations: Whenever content(s) of a cell or range of cells are changed, the formulas dependent on them and Volatile functions are recalculated.
You can increase the speed of some procedures by keeping screen updating turned off.
Without a good logic, a good written VBA macro program has no value.
Analyze the logic: Before optimizing the syntax, pay more attention in optimizing the logic.