Undo an Excel macro

[pe]

I have the following macro to delete blank rows in an Excel worksheet...

Public Sub DeleteBlankRows()

Dim R As Long
Dim C As Range
Dim N As Long
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
N = 0
For R = Rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(R).EntireRow) = 0 Then
Rng.Rows(R).EntireRow.Delete
N = N + 1
End If
Next R

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

How can I add a procedure to undo the above macro and put the worksheet back with all the blank rows it just took out? Thanks for the help.

 

[ide]

my only one idea (i don't know how to append macro actions, and orig values to the undo list):
copy your activeSheet to an invisible tempSheet into your workbook. If you want to undo macro, delete activeSheet and rename tempSheet to activeSheet.name. Create a close event macro with command to delete tempSheets. This restore the state of wsheet before creating tempSheet.

 

[pe]

sounds like a good idea. i'll try it. thanks for your help