Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Clearing the Undo Stack in a Macro.
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:
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: 
Sub ClearUndo()
Range("A1").Copy Range("A1")
End Sub
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9733) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Clearing the Undo Stack in a Macro.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
Need to rename a file in a macro? It's easy to do using the Name command, as discussed in this tip.
Discover MoreYou can manually copy macros from one workbook to another, but what if you want to automate the copying process? Here's ...
Discover MoreWhen creating macros, you often have to know how to display individual worksheets. VBA provides several ways you can ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2019-09-12 05:42:40
Adrian Gill
Hi, great tip, but Is there any other method to flush the undo stack?
I seem to have a corrupted undo stack, the workbook has many macros, yet none are clearing the problem I have when they run.
The problem is, for example, I clear cell A1 then use Ctrl+Z to undo it. The undo works, but also places unwanted formatted entries in two other cells (specifically D13 & E20 )
It's a multi sheet workbook and this effect is repeatable on all sheets. It even occurs when all I do is open it and then do and undo a trivial change.
2018-03-12 02:36:52
Eric Franklin
Here is a tip from the 2/3/18 newsletter. Maybe this will help.
https://excelribbon.tips.net/T012513_Preserving_the_Undo_List.html
2015-03-04 16:12:06
Glenn Case
Brian:
If we could copy the undo stack & reapply after a macro, that might not achieve what you want... depending on what the macro is doing, those undo actions may wreak havoc. For instance consider a case in which the last action was to make C5 = 10. If I run a macro which deleted row b entirely, and then I apply the undo action from before the macro, then it might be applied against C5, which used to be D5 before the macro; not what was intended. If the macro actions were saved to the stack, a macro might easily overload the stack such that only some of the undo actions would be available, again resulting in a mess...
If you think about the issues associated with this, it maybe becomes more apparent why Excel acts the way it does.
I have on occasion written an undo macro to reverse the changes wrought by a specific macro, so I can undo the macro action only. However, this doesn't address other undo actions which might have been in the stack before the macros were run.
2015-03-04 07:52:02
Small correction: Ctrl+Z still works if your macro changes the cell format only.
It isn't always consistent though. I think it is restricted to format changes done through Worksheet_Change macros; but I haven't really explored the subject.
@Bruce
Undo stack is reset upon closing the workbook, so you have nothing to fear.
@Khushnood
Indeed, undo stack is application specific and local. If you run a macro, you can no longer Undo any of your open workbooks.
@Brian
...Don't we all.
Now seriously, if you need to keep a copy of your data around the best solution is to have a hidden sheet where every change in the main sheet is reported. This is incredibly slow since the copying macro should trigger on Selection_Change.
2015-03-03 09:33:27
Brian
I would like to know how to copy the undo stack and reapply it after the macro completes.
2015-03-02 02:13:15
Khushnood Viccaji
I haven't tried this tip yet, so can't comment on its usefulness (or otherwise).
However, Bruce Conn's comment about not wanting to share "what might have been" with others, is not clear to me.
I may be completely wrong about it, or missing something that I haven't heard of before, but... I thought that the Undo Stack is a locally stored, and session-specific (even action-specific) tracking mechanism in Excel (and Word as well).
Once I finish working on my Excel (or Word) file, save, close and email it to someone, how is it possible for the other person to use the 'Undo Stack history' from my computer?
2015-03-01 08:02:04
Willy Vanhaelen
Obviously I overreacted. Many times I saw the complaint that Excel clears the undo stack after running a macro and that often bothers me too. But I wasn't aware that sometime it could be beneficial. Never too old to learn...
2015-02-28 16:56:00
Chris Young
Willy, all that I know is that I have deeply nested conditional loops running across a few 100 thousand rows of data in macro 1, I'd run macro 2 and, prior to using this stack clearing method, it was running like a dog (if I didn't clsoe and restart Excel). Once applied, a process that took over 10 mins to complete took about 20 secs. As I say, brilliant solution for my purposes. No question. "If you've got nothing positive to say, you're better to stay silent...".
2015-02-28 15:51:27
Bruce Conn
Willy, speak for yourself. I run 'what-if' scenarios on a division's proposed salary updates: increases, bonuses, and terminations. After I settle on what I want, HR takes it from there. Do I want someone in HR poking around by 'undoing' my work to see what 'might have been' or who may be candidates for termination?
So many other examples.
Peace.
2015-02-28 08:29:31
Willy Vanhaelen
This is the most stupid tip I ever saw. Mostly users complain that the undo stack it is cleared by the simple fact of running a macro.
This very useful undo stack is there in case you need it and bothers me in no way. I even use it very frequently, often for testing purposes. Why should I clear it?
2015-02-27 20:37:26
Chris Young
Wooshka! Applied. Instant fix. Brilliantly simple solution. Love it. Kicks proverbial on some of the rubbish I've read in the last 15 minutes..
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2019 Sharon Parq Associates, Inc.
Comments