CODEWITHSUNDEEP
excelvba
gizgok
gizgok

Reputation: 7649

How to record delete event in excel

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.

Upvotes: 1

Views: 7270

Answers (3)

Ryan
Ryan

Reputation: 11

Private Sub Worksheet_Change(ByVal Target As Range)
    'UsedRange works fine provided there is always something in Row1
    'Declare varY from a Module and set from Workbook_Open or Sheet.Activate,
    'or undo first sheet change, but set variable

    If Target.Columns.Count = 16384 Then
        Select Case Sheet1.UsedRange.Rows.Count
            Case Is > Sheet1.Range("" & varY & "").Rows.Count
                MsgBox "insert row"
                Sheet2.Rows(Target.Rows.Address).Insert Shift:=xlDown, _
                    CopyOrigin:=xlFormatFromLeftOrAbove
            Case Is < Sheet1.Range("" & varY & "").Rows.Count
                MsgBox "delete row"
                Sheet2.Rows(Target.Rows.Address).Delete Shift:=xlUp
        End Select
    Else: Sheet2.Range(Target.Address).Value = Target.Value
    End If
    varY = Sheet1.UsedRange.Address
End Sub

Upvotes: 1

Simon Cowen
Simon Cowen

Reputation: 1903

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.

Upvotes: 0

Dick Kusleika
Dick Kusleika

Reputation: 33165

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.

Upvotes: 0

Related Questions