Search code examples
exceleventstriggersworksheet

Excel Worksheet Change Event Not Going when multiple cells deleted


I have code in place to trigger a change event which will resort my data. THe chart has information in Columns A-D. The code triggers the data to be resorted anytime a value in column D changes. This works when I enter new data as well as when I delete the contents of a cell in Column D, however, if I select the information in Columns A-D of a certain row and then hit delete, deleting the contents of all four cells with data in that row the event doesn't trigger. What do I need to change to make this happen? Code is

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Then
    Application.EnableEvents = False
Range(Cells(1, 1), Cells(90, 1)).Select
    ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range(Cells(2, 1), Cells(90, 1)) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet2").Sort
        .SetRange Range(Cells(1, 1), Cells(90, 4))
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("E4").Select
Application.EnableEvents = True
End If
End Sub

Solution

  • You used:
    If Target.Column = 4 Then

    Try using:
    If Not Intersect(Target, Range("D:D")) Is Nothing Then