Delete Rows Macro With Undo

miketurn

Active Member
Joined
Dec 8, 2016
Messages
268
Hello Everyone,

Another task I find myself doing a lot of is deleting rows. I perform this function with the "DELETE ROWS" tool button, which is okay but find it a little uneffecient the amount that I use it and have to mouse up to it each time. I am aware that you can also right click and delete as well but even that I am not a fan of.

My goal was to be able to map the "DELETE ROWS" tool to one of the Function Keys which I have been successful in doing, but the thing is as we all know once you use a macro it cannot be undone.

My question is does anyone by any chance have an already made "DELETE ROWS" macro that contains the proper coding to UNDO the command? Or know of any other way to map this tool to a Function Key and be able to UNDO the command if a mistake is made?

Thank You to anyone who reads this
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Assuming your using Windows and a relatively recent version?
Rather than trying to create a macro I think the easiest way is to add the command to the Quick Access toolbar (QAT) and then use the ALT key combination that is automatically created.

In Excel, right click the QAT and select Customise Quick Access toolbar
In the left hand side, set 'Choose commands from' to 'All commands', then scroll down to 'Delete Sheet Rows', click on it and click Add>> and OK

Now in Excel, if you press and release the ALT key a series of numbers will appear above each button in the QAT. Press the corresponding number (eg 4) on your keyboard and it will run the 'Delete rows' command. This can be undone using CTRL-Z or the undo command.

Regards

Murray
 
Upvote 0
Ahh, unfortunately I am using 2000, I thought I mentioned that but didn't.

I was hoping someone had something like this already made for themselves.
I find myself deleting many rows, so a hotkey assigned to this function would be great, as I mentioned, I have been able to create one but sometime scared to use it because if I get a little to trigger happy with it I may delete something I don't want to.

Thank You for your response
 
Upvote 0
You can't UNDO once a macro has been run......VBA clears the UNDO stack !!
If you provide the criteria for deleting the rows, we can write a macro for you !
 
Upvote 0
Michael...I have always thought it possible, but never had a reason to actually try it. But couldn't you have the macro save the information and its cell address to variables before deleting row, and then if a created "undo" button is pressed it would restore the deleted info?
 
Upvote 0
@dUBBINS
I may have been a bit too general with my comment....it's darned hard to UNDO !!
AND I'd be looking at a better option than UNDOING a macro......maybe get the original macro right before you remove data !!!


Have a read here for what's involved !!


Excel: Undo With Excel VBA



An easier option would be to close the workbook without saving....and start again !!!
 
Last edited:
Upvote 0
Try this:
But this will not solve your "undo" request:

To delete a row just double click any place on the row.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Rows(Target.Row).Delete
End Sub

OK
 
Last edited:
Upvote 0
@Michael...Thank you for that article. And wow. Glad I never came across a reason to attempt that. The only reason I ever thought about an undo, was because I worry about people manually starting a macro on a page it doesn't belong on. But that is easily solved by saving frequently. Take care.
 
Upvote 0
@dUBBINS
Probably more importantly, would be to get the macro correct.....and within that macro maybe insert Inputboxes / msgboxes with warnings AND errortrapping
 
Upvote 0
Don't remember where I came across this code, but I've had it for a while. Seems to work well. Probably wouldn't work too well on large datasets.

Code:
'Custom data type for undoing
    Type SaveRange
        Val As Variant
        Addr As String
        Cidx As Integer
        cW As Integer
        cH As Integer
    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(ActiveSheet.UsedRange.Count)
    Set OldWorkbook = ActiveWorkbook
    Set OldSheet = ActiveSheet
    i = 0
    For Each cell In ActiveSheet.UsedRange
        i = i + 1
        OldSelection(i).Addr = cell.Address
        OldSelection(i).Val = cell.Formula
        OldSelection(i).Cidx = cell.Interior.ColorIndex
        OldSelection(i).cH = cell.RowHeight
        OldSelection(i).cW = cell.ColumnWidth
    Next cell
            
'   Insert 0 into current selection
    Application.ScreenUpdating = False
    
'   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
    OldWorkbook.Activate
    OldSheet.Activate
    
'   Restore the saved information
    For i = 1 To UBound(OldSelection)
        Range(OldSelection(i).Addr).Formula = OldSelection(i).Val
        Range(OldSelection(i).Addr).Interior.ColorIndex = OldSelection(i).Cidx
        Range(OldSelection(i).Addr).RowHeight = OldSelection(i).cH
        Range(OldSelection(i).Addr).ColumnWidth = OldSelection(i).cW
    Next i
    Exit Sub
'   Error handler
Problem:
    MsgBox "Can't undo"
End Sub

Put the ZeroRange as the first line before the code you are going to run. Then, if you need to undo it, run the UndoZero Sub.
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top