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)

10

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

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

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

Calculating Combinations

The COMBIN function is used to determine the number of combinations that can be made from a group of elements. This tip ...

Discover More

Jumping Around Folders

When you open a workbook in Excel, the Open dialog box always starts within the folder in which you were last working. ...

Discover More

Creating a Copy without Formulas

Excel makes copying worksheets (duplicating them) rather easy. However, you may want a worksheet copy that differs from ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Getting a File Name

Does your macro need to allow the user to specify a particular file name that should be used by the macro? Here's a quick ...

Discover More

Stepping Through a Non-Contiguous Range of Cells

Using macros to step through each cell in a selection is a common occurrence. What if that selected range is made up of ...

Discover More

Getting User Input in a Dialog Box

Want to get some input from the users of your workbooks? You can do it by using the InputBox function in a macro.

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 9 + 9?

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

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.