I am using the below code to intersect change on column E with some conditions, one of the conditions is to firing if target changed value is not null , the effect of event applied without problem, But If two cells (or more) selected and deleted on the same row of target I got this error

Run-time error 13: Type mismatch

This the cause of error Target.value <> ""

Private Sub Worksheet_Change(ByVal Target As Range)
 
   If Not Intersect(Target, Range("E:E")) Is Nothing And _
      Target.Columns.Count = 1 And _
      Target.Row > 1 And _
      Target.value <> "" Then
 
     Application.EnableEvents = False
       'Some codes here
     Application.EnableEvents = True
 
    End If
 
End Sub

Appreciate for yours comments and answers.

CodePudding user response：

You only check Target.Columns.Count but Target can also be multiple rows. And then Target.value is an array of values, and an array cannot be compared to = "" without looping.

So replace Target.Columns.Count = 1 with Target.Cells.CountLarge = 1 to ensure that Target is only one cell, or alternatively loop through all the cells in Target that intersect with your range E:E.

Private Sub Worksheet_Change(ByVal Target As Range)
 
    Dim AffectedRange As Range
    Set AffectedRange = Intersect(Target, Me.Range("E:E"))

    If Not AffectedRange Is Nothing Then
        Dim Cell As Range
        For Each Cell in AffectedRange
            ' here you can handle each cell that has changed in E:E
        Next Cell
    End If

End Sub

Off topic note:

It you use Application.EnableEvents = False make sure that if an error occurs you turn the events back on! Otherwise your events are turned off in the entire Excel until you close it.