Skip to content

Instantly share code, notes, and snippets.

Created September 6, 2017 02:26
What would you like to do?
'Custom data type for undoing
Type SaveRange
Val As Variant
Addr As String
End Type
' Stores info about current selection
Public OldWorkbook As Workbook
Public OldSheet As Worksheet
Public OldSelection() As SaveRange
Sub ZeroRange()
' Inserts zero into all selected cells
' Abort if a range isn't selected
If TypeName(Selection) <> "Range" Then Exit Sub
' The next block of statements
' Save the current values for undoing
ReDim OldSelection(Selection.Count)
Set OldWorkbook = ActiveWorkbook
Set OldSheet = ActiveSheet
i = 0
For Each cell In Selection
i = i + 1
OldSelection(i).Addr = cell.Address
OldSelection(i).Val = cell.Formula
Next cell
' Insert 0 into current selection
Application.ScreenUpdating = False
Selection.Value = 0
' Specify the Undo Sub
Application.OnUndo "Undo the ZeroRange macro", "UndoZero"
End Sub
Sub UndoZero()
' Undoes the effect of the ZeroRange sub
' Tell user if a problem occurs
On Error GoTo Problem
Application.ScreenUpdating = False
' Make sure the correct workbook and sheet are active
' Restore the saved information
For i = 1 To UBound(OldSelection)
Range(OldSelection(i).Addr).Formula = OldSelection(i).Val
Next i
Exit Sub
' Error handler
MsgBox "Can't undo"
End Sub
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment