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