Hello there,

Requirement: I would like to capture the "event" of rows being deleted in a sheet, in particular I need to capture the first and last row being deleted.

I'm really struggling with this as I have been at it for the last two days. I have tried to do this with the below code but it does not work consistently - sometimes the (2) code is reached and sometimes not. I'm sure there must be a better way to satisfy my requirement. Could anyone please provide a suggestion?

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    MsgBox "(1) Worksheet change event triggered"

    ' Detect "Deletion of Rows" event
    If Target.Columns.Count = Columns.Count And WorksheetFunction.CountA(Selection) > 0 Then
        MsgBox "(2) My 'Deletion of Rows' event triggered"
        
        Dim firstRow As Integer: firstRow = Target.Row
        Dim lastRow As Integer: lastRow = Target.Row + Target.Rows.Count - 1
                
        ' Do something with firstRow and lastRow
    End If
End Sub
Thanks in advance,
Michelle