Run-time error 13: Type mismatch , If two cells (or more) selected and deleted on the same row of target

Question

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.

Answer 1

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.