Capture Deleted Rows

Excel doesn’t provide events for the deletion of rows and columns. There are two methods that you can use to determine if a user deletes a row, and they are described here. If you want to detect column deletion, the process is the same, but some of the details change.

Monitored Public Variable

The first method counts the number of rows used in the spreadsheet, then recounts whenever something is changed. It uses the Worksheet_Change event which fires whenever the user changes any cell in the worksheet including row deletion. Start by creating a public variable in a standard module:

Public glOldRows as Long

This will hold the number of rows used in the spreadsheet before the change. We’ll compare it to the number of rows after the change to determine if one has been deleted. In the worksheet’s class module, set up an activate event and a change event.

Private Sub Worksheet_Activate()
    glOldRows = Me.UsedRange.Rows.Count
End Sub
 
Private Sub Worksheet_Change(ByVal Target As Range)
   
        If Me.UsedRange.Rows.Count < glOldRows Then
            MsgBox “Row deleted”
        End If
   
    glOldRows = Me.UsedRange.Rows.Count
   
End Sub

Use the dropdown boxes at the top of the code window to insert the Sub and End Sub statements. The activate event sets the initial value of the public variable whenever the sheet is activated. In the change event, the current number of rows are counted and compared to the public variable to determine if there are fewer.

One downside to this method is that you can’t prevent the deletion, only know that it happened. There’s also a lot of overhead associated with this method. The change event is fired every time something on the sheet is changed. You could work for days or years on this worksheet changing cells without deleting a row. All those precious computer cycles wasted.

Custom Class Module

The second method attempts to capture all the ways that a user can delete a row. The danger of this method is that you may miss a way. I can only see two ways to delete a row: the Delete control on the Row right click menu and the Delete control under the Edit menu. I don’t know of any keyboard shortcuts to delete an entire row.

To use this method, we’ll create a custom class module with two variable; one for each of the controls. We will then monitor the click events of those commandbar controls to determine if the user clicked them. Start by creating a custom class module (Insert > Class Module) . Rename it to CCbarEvents in the Properties Window (F4).

Next create a global variable to hold your custom class module – create this in a standard module:

Public gclsCbarEvents As CCbarEvents

and use the workbook’s open event to initialize the class module whenever the workbook is opened:

Private Sub Workbook_Open()
   
    Set gclsCbarEvents = New CCbarEvents
   
End Sub

Now the class module will be “live” as long as the public variable is in scope, i.e. for as long as the workbook is open. Next we have to put some stuff in the class module that does some work.

Private WithEvents mRowDelButton As CommandBarButton
Private WithEvents mCellDelButton As CommandBarButton
 
Private Sub Class_Initialize()
 
    Set mRowDelButton = Application.CommandBars.FindControl(, 293)
    Set mCellDelButton = Application.CommandBars.FindControl(, 478)
   
End Sub
 
Private Sub mCellDelButton_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
   
    If TypeName(Selection) = “Range” Then
        If Selection.Address = Selection.EntireRow.Address Then
            MsgBox Selection.Rows.Count & ” Row” & _
                IIf(Selection.Rows.Count = 1, “”, “s”) & ” deleted.”
        End If
    End If
   
End Sub
 
Private Sub mRowDelButton_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
   
    MsgBox Selection.Rows.Count & ” Row” & _
        IIf(Selection.Rows.Count = 1, “”, “s”) & ” deleted.”
       
End Sub

I declare two variables using the WithEvents keyword to expose the events to our class module. The variables are assigned to the appropriate commandbar controls in the class’ initialize event. I found the IDs of the commandbar controls by going to the immediate window and typing:

?application.CommandBars(“Row”).Controls(“&Delete”).Id
 293
?application.CommandBars(1).Controls(“&Edit”).controls(“&Delete…”).id
 478

Note that the caption for Edit > Delete changes if a row or column is selected instead of a cell. The class’ initialize event is fired when I establish the class in the Workbook_Open event. Next I create the click events for the CommandBarButton variables. For mCellDelButton, I have to first make sure that an entire row (or rows) is selected because this command can be used to delete columns and non-row/column ranges, as well as a bunch of other stuff. I make sure that what is selected is a Range and that the address of the range is the same as the entire row(s).

For mRowDelButton, I know that a whole row is selected, because you can’t show that menu otherwise. The action in each procedure is a message box, but you’ll likely need something more substantive. Don’t forget that you can set the CancelDefault argument of these event procedures to True to prevent the deletion from happening.

If I missed a way that a user can delete a row, or you have a better method for detecting row deletions, please leave a comment.

Posted in Uncategorized

34 thoughts on “Capture Deleted Rows

  1. I was about to ask if it works with ctrl minus, but I assume it would not as it is looking at menus used.

    is there any way to have a class look at keyboard combos (ctrl + x, ctrl + shift + x, etc.)

  2. I think you could catch the Ctrl – or Ctrl + shortcuts using Application.OnKey().

  3. Dang, that makes things more complicated. (BTW, shift+space selects a row). If I use OnKey to capture that shortcut, I have to account for all the other things a user might want to delete. I can live with that, but I can’t figure out how to let the user delete a non-row range. In the UI it asks which way to shift the cells. In code,

    Selection.Delete

    doesn’t ask, but rather assumes based on the shape of the range. How do I reproduce the UI experience in code? Anyone?

  4. well could you not checl on ctrl minus of the selection is actualy a row or not ?

    that should probably work

  5. Dick, I always customize my standard toolbar by adding the insert/delete row/column buttons, so I’ll have 2 controls with ID 293. I don’t think your code will catch both (?).

    This seems to catch them all, including ones in submenus. To test, I put one on the Edit/Fill menu – it caught it twice, once from Edit, once from Fill. I aslo note though that if somebody has more than one copy of the control on a toolbar (why, I don’t know) this will only catch the first:

    Sub test()
    Dim cbar As CommandBar
    Dim mRowDelButton As CommandBarButton

    For Each cbar In Application.CommandBars
    Set mRowDelButton = cbar.FindControl(ID:=293, recursive:=True)
    If Not mRowDelButton Is Nothing Then
    ‘in case it’s in a sub-menu – index will error
    On Error Resume Next
    Debug.Print mRowDelButton.Parent.Index, mRowDelButton.Parent.Name, mRowDelButton.Caption
    If Err.Description = “Method ‘Index’ of object ‘CommandBar’ failed” Then
    Debug.Print mRowDelButton.Parent.ID, mRowDelButton.Parent.Name, mRowDelButton.Caption
    End If
    End If
    Next cbar

    End Sub

    Just to make it more interesting, in the immediate window, I notice that there are two row menus:
    389 Fill Delete &Rows
    4 Formatting Delete &Rows
    38 Row- &Delete
    41 Row- &Delete
    389 Fill Delete &Rows

    I also note that if there is more than one version

  6. Here’s what I have:

    Sub DelRow()
       
        If TypeName(Selection) = “Range” Then
            If Selection.Address = Selection.EntireRow.Address Then
                Selection.Delete
                MsgBox Selection.Rows.Count & ” Row” & _
                    IIf(Selection.Rows.Count = 1, “”, “s”) & ” deleted.”
            Else
                Selection.Delete
            End If
        End If
       
    End Sub

    I don’t know how to get a return value from the

    Dialogs(xlDialogEditDelete).Show

    in order to pass the correct argument to

    Selection.Delete

    .

  7. Depends on your definition of “DELETE”, but if you Cut a row, and then Insert Cut Cells onto a different sheet, you’ve blanked out a row…

  8. My earlier comment seems to be in moderation limbo. I’ll try to repeat it:

    When I run your code to find the 293 control it only finds one instance – I always put it on my Formatting toolbar as well, and it only finds that one.

    The code below finds all instances of 293, I think, including ones on submenus. To test, I put a 293 on the Edit – Fill menu. This code finds it twice, once from the Edit menu, once from Fill.

    Interestingly (I’ve seen this before but forgotten about it) it finds 2 Row menus, both with control 293. Also, this code won’t find a second copy of 293 on the same toolbar (No, I don’t do that :))

    Sub test()
    Dim cbar As CommandBar
    Dim mRowDelButton As CommandBarButton

    For Each cbar In Application.CommandBars
    Set mRowDelButton = cbar.FindControl(ID:=293, recursive:=True)
    If Not mRowDelButton Is Nothing Then
    ‘in case it’s in a sub-menu – Index will error
    On Error Resume Next
    Debug.Print mRowDelButton.Parent.Index, mRowDelButton.Parent.Name, mRowDelButton.Caption
    If Err.Description = “Method ‘Index’ of object ‘CommandBar’ failed” Then
    Debug.Print mRowDelButton.Parent.ID, mRowDelButton.Parent.Name, mRowDelButton.Caption
    End If
    End If
    Next cbar
    End Sub

    Immediate Window:
    389 Fill Delete &Rows
    4 Formatting Delete &Rows
    38 Row &Delete
    41 Row &Delete
    389 Fill Delete &Rows

  9. Dick, I completely misunderstood how it worked. You’re absolutely right and it works. Thanks.

    I have noticed that sometimes instead of 293, Delete is 292, although I’ve never figured out the pattern.

  10. Dick, I apologize for not actually running your code before critiquing it – I could have at least saved myself some embarrasment. Now that I have run it, I realize just how poorly I understand classes.

    To wit, when I read the help for FindControl, it says “If the CommandBars collection contains two or more controls that fit the search criteria, FindControl returns the first control that’s found.” Also, when I add form buttons to a class, I have to add them one at a time (I think).

    So can you explain to a simpleton like me how the Initialize event in your class refers to all the 293 controls?

  11. In Excel 2003, if you try to count the rows in the used range, it prevents Application.Undo from working. But if you have a named range on the worksheet, you can access its Address property without preventing the Undo.

    Private Sub Worksheet_Change(ByVal Target As Range)
    ‘Prevents inserting or deleting rows or columns
    Static adr As String
    Dim str As String
    Dim rg As Range
    Set rg = Range(“A1:IU65535?)
    str = “PartOfWorksheet_BeingWatched”
    Application.EnableEvents = False
    If adr = “” Then
    rg.Name = str
    adr = Range(str).Address
    ElseIf Range(str).Address adr Then
    Application.Undo
    MsgBox “You are not permitted to insert/delete columns or rows”, vbExclamation
    Else
    adr = rg.Address
    ActiveWorkbook.Names(str).RefersTo = “='” & ActiveSheet.Name & “‘!” & adr
    End If
    Application.EnableEvents = True
    End Sub

  12. Dick

    I suggest that you dont use the UsedRange.Rows.Count
    It doesnt always return accurate results.

    I prefer
    EndRow = Cells.Find(“*”, After:=Range(“IV65536?), searchorder:=xlByRows, searchdirection:=xlPrevious).Row

    ….to get the last used row.

    Regards
    Sam

  13. Doug,

    If you have a copy of PED, read pages 237-239. To summarise, when you hook a built-in button’s events in the way Dick shows, you’re not actually hooking *that button*, but rather that button’s *ID* – so all instances of buttons with the same ID fire the event procedure. For custom buttons, it’s the Tag that gets hooked rather than the ID (so all buttons with the same Tag fire the single event procedure).

    Regards

    Stephen Bullen

  14. Although the majority of the technical side of the debate above has gone straight over my head (with a nice *WHOOSH* sound), I would just like to express my thanks, to Dick & all others contributing.
    A number of tables etc in use have had issues in the past with users deleting a row “because they put something in wrong/twice/got bored/wanted to see what would happen” etc, which then throws out other dependent calculations, validation lists, or whatever, and makes life awkward… Making use of the suggestions here allows me to identify when this has happened, identify the user concerned, and go give them a good kicking. Which is always a worthwhile way to educate people in the protocols of filling things in. :)

    Many Thanks!

  15. Stephen, I do have PED. I know I’ve read those pages but I’ll read them again. I’m sure that now I’ll remember this technnique. Very cool. My thanks to you and Dick.

  16. I just used this instead of OnAction for custom toolbar buttons in the VBE. From what I could Google, OnAction doesn’t work for VBE toolbar buttons. Or else I just didn’t guess the right string format, – I always struggle with those OnAction strings, trying to get the single-quotes, double-quotes and exclamation marks in the right place.

    Anyways, this technique worked a treat.

  17. I went back and read Excel 2002 VBA, which confirmed that OnAction doesn’t work for the VBE toolbar buttons.

    I have another question about this – posted to the ng yesterday but no response:

    I’m using this technique to manage buttons relating to a worksheet that’s protected with AllowFiltering. I want the user to be able to turn Filter on and off, which they can’t in a protected sheet. The only way I’ve found to do so is to set a dummy OnAction for the filter button. Specifying OnAction (or any other button property) then makes the button available, but it’s always so, e.g, even when the focus is a chart. I’ve tried the technique of copying an existing button that has the desired state performance, but once I add a tag it also becomes avaibable all the time. Is there a better way? Here’s the class module code:

    Public WithEvents filter_class_button_899 As CommandBarButton

    Private Sub Class_Initialize()
    Set filter_class_button_899 = Application.CommandBars.FindControl(ID:=899)
    filter_class_button_899.OnAction = “dummy_sub”
    End Sub

    Private Sub Class_Terminate()
    filter_class_button_899.Reset
    End Sub

    Private Sub filter_class_button_899_Click(ByVal Ctrl As
    Office.CommandBarButton, CancelDefault As Boolean)
    Call toggle_filter ‘basically “range.Autofilter”
    filter_class_button_899.State = Not filter_class_button_899.State
    End Sub

  18. Juan Pablo,

    Thanks. What I am trying to do is enable the Data>Filter>AutoFilter button. What is described above seems to be the pre-2002 version of Protect AllowFiltering := True, which I’m using (in XL 03). Both of these seem to allow the user to use a filter that’s already set, but not to set and unset a Filter, i.e, click on the AutoFilter button. Of course, I’m probably just not understanding, so please help me out. :)

  19. Interesting problem…

    My solution is a little restrictive, but if you have a defined range (not necessarily named, but just a critical area)that you want to monitor, fill an unused adjacent range (Column if rows are of interest, and vice versa for columns) with numbers, and examine the sum. If the sum is different after the change event fires, you can [carefully] assume the row (or column) was deleted, and proceed with the msgbox and undo. There may be some holes in this scheme, but it works in my limited case. I hide the column that contains the numbers to avoid prying eyes…

  20. I have looked through most of these examples; they are all good but I am not sure that I still see wht I am after.

    I am working with some named ranges.

    On my change event, I have some code that looks like this:

    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Rng1 As Range

    Set Rng1 = Range(“Sh1billsW”).Resize

    If Rng1.Rows.Count + 1 Then
    MsgBox “Row Added”
    MsgBox “Number of Rows = ” & Rng1.Rows.Count
    Else
    If Rng1.Rows.Count – 1 Then
    MsgBox “Row Deleted”
    Exit Sub
    End If
    End If

    End Sub

    It is almost there.
    If I add or delete a row, it does return the proper row count in the named range.
    However, anytime I do anything in the worksheet, it still runs that code.
    I only want the code to run in the (event) that a row is inserted or deleted

    Any ideas?

    Thanks
    Mark

  21. Hello Everyone,

    First of all, thanks for all your comments, I learned a lot.

    One comment to Dick’s first method (you know the one where you check events after they happen), and which has the major drawback of not being able to prevent them.

    You can actually make it right by simply rejecting any change that you don’t explicitly allow.

    The code to do that:

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim TargetWs As Worksheet
    Set TargetWs = Sh

    If( … ‘ I don’t like what the user is doing… ) Then
    Application.EnableEvents = False
    Application.Undo
    Application.EnableEvents = True
    End If
    End Sub

    This method also has a downside. If some code runs in the meantime that clears the undo buffer, the undo fails.

    Best regards,
    Balint

  22. What about the new 2007 ribbon? This doesn’t trap the event when clicking the Home -> Delete ribbon button. Any ideas on this?

    – Bob

  23. Hey Bob,

    Did you get a response to your query regarding excel 2007 on any other forum? Or did you figure it out?

    Thanks
    -Jairam

  24. I used this to disable row adding/deleting:

    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Rng1 As Range
    Set Rng1 = Range(“ZONADATOS”).Resize

    If Rng1.Rows.Count + 1 Then
    MsgBox “¡cant add rows!”
    Application.EnableEvents = False
    Application.Undo
    Application.EnableEvents = True
    Exit Sub
    Else
    If Rng1.Rows.Count – 1 Then
    MsgBox “¡cant delete rows!”
    Application.EnableEvents = False
    Application.Undo
    Application.EnableEvents = True
    Exit Sub
    End If
    End If
    end sub

  25. IN THE END; ENDED UP USING:

    Public glOldRows As Long
    Private Sub Worksheet_Activate()

    glOldRows = DataBase.Range(“ZONAAA”).Rows.count

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rng1 As Range
    Dim RngValue
    Set Rng1 = Range(“ZONAAA”)

    If Rng1.Rows.count glOldRows Then
    MsgBox “¡No se pueden modificar las filas!”
    Application.EnableEvents = False
    Application.Undo
    Application.EnableEvents = True
    Exit Sub
    End If

  26. Dick – thank you for your code. First of all, let me say that I couldn’t get the first example (Worksheet_Change) to work properly. It kicked in when the user right-clicked on the row, but at that time, the row HADN’T been deleted. Once the Worksheet_Change had run, I could THEN delete the row, but the Worksheet_Change didn’t kick in for the actual deletion of the row.
    I then tried the class exampe, and that worked perfectly. I should add that I’m running Excel 2007 with XP and have customized the ribbon so that the Edit options are disabled.

    Igor’s probably moved on since then, but I couldn’t get his example to work either. I kept getting a 424 on the line gl_OldRows = database.Range(“ZONAAA”).Rows.Count

    Thanks again Dick


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.