How to record delete event in excel

Question

I have a situation where I need to know if manually a delete was performed on any row in my spreadsheet.How can I record this event in vba.

Answer 1

Excel doesn't provide a good way to determine if a row or column has been deleted. One way you can do it is using a defined name and the Calculate event. If you care about the first 1,000 rows, for example, name cell A1001 as "rngLastRow". Then use this code in the sheet's calculate event

Private Sub Worksheet_Calculate()

    If Me.Range("rngLastRow").Row < 1001 Then
        MsgBox "Row deleted"
    End If

End Sub

It won't tell you which row was deleted, so you'll need something different if you need to know that.

If you want to simply prevent people from deleting rows, you could use an array formula in a hidden column. Again, assuming your app uses the first 1,000 rows, select A1:A1000 (or some other empty column) and type =1 and commit with Control+Enter. That will create an array in A1:A1000. When the user tries to delete a row, they will get the unhelpful message "You cannot change part of an array.", but they won't be able to delete the row.

Answer 2

You could always do something like:

Public lngPreviousRow As Long
Public lngCurrentRow As Long

Private Sub Worksheet_Calculate()
    lngCurrentRow = Me.Range("rngLastRow").Row

    If lngCurrentRow  < lngPreviousRow Then
        MsgBox "Row deleted"
    End If

    lngPreviousRow = lngCurrentRow
End Sub

So long as you set up lngPreviousRow to the intially (Workbook_Open e.g) it will always detect a row deletion, regardless of any row inserts etc.