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.

Clearing the Undo Stack in a Macro

by Allen Wyatt
(last updated February 28, 2015)

9

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 (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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Inserting a Document's Path

You can use the FILENAME field to insert a document's filename and, optionally, the path to that filename. However, if you ...

Discover More

Repeating Rows at the Bottom of a Page

Excel allows you to repeat rows at the top of every page of a printout. If you want to repeat rows at the bottom of every ...

Discover More

Smushing Text Together

Word gives you control over how your text appears on the page. This includes adjusting how close letters are to each other ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Running Macros in the Background

Want to run a macro in Excel, but not sure if doing so will tie up your computer? Here's how macro processing really happens.

Discover More

Copying Worksheet Code Automatically

When creating a workbook to be used by others, you may want any worksheets they add to the workbook to contain some special ...

Discover More

Understanding Functions in Macros

Functions are a common programming construct. They help you to create easy ways of processing information and returning a ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 6 + 0?

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

balthamossa2b

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..


This Site

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.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.