Clearing the Undo Stack in a Macro

Here is a tip from the 2/3/18 newsletter. Maybe this will help.

https://excelribbon.tips.net/T012513_Preserving_the_Undo_List.html

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.

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.

I would like to know how to copy the undo stack and reapply it after the macro completes.

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?

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

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

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.

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?

Wooshka! Applied. Instant fix. Brilliantly simple solution. Love it. Kicks proverbial on some of the rubbish I've read in the last 15 minutes..