Clearing the Undo Stack in a Macro

Summary: The Undo stack is cleared automatically whenever you run a macro. This fact of Excel's behavior allows you to easily clear the stack from within the macro itself. (This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, Excel 2003, and Excel 2007.)

In Word, you can use the UndoClear method with the ActiveDocument object in order to clear the Undo stack. If you are programming macros in Excel's flavor of VBA, you may wonder if there is a similar method for use in Excel.

Excel VBA doesn't provide a method like UndoClear. The reason is because the undo stack is automatically cleared by Excel whenever your macro makes a change (any change) to the workbook. If your macro doesn't make any changes, and you still want it to clear the undo stack, then all you need to do is make an innocuous change to the worksheet. For instance, the following macro copies the contents of cell A1 back into A1, and in the process clears the undo stack:

Sub ClearUndo()
    Range("A1").Copy Range("A1")
End Sub

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2463) applies to Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Make Home Buying Less Stressful! Why make home buying harder than it needs to be? Put your mind at ease—discover all the questions you need to ask to make the best buying decision.

Check out Buying a Home Checklist today!

Helpful Links

Newest Tips

Subscribe

RSS Feeds

Clearing the Undo Stack in a Macro