Excel-VBA - How to identify Target range more than 1 cell is deleted in a Worksheet Change function

Question

I'm attempting to determine whether the worksheet change function will cause cells in the same rows in column X to also be deleted if a user deletes values from certain cells in column B.

IsEmpty(Target) returns true when I delete just one cell in column B, allowing me to erase the identical row cell in column X.

IsEmpty(Target) returns False when many cells in column B are selected and the delete button is pressed. Target is currently a range of several cells. Simply put, I am unable to determine whether a user simultaneously erased a range of numbers in column B. Any assistance would be greatly valued.

Below code works when one cell is deleted but not when a range of cells are deleted.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column <> 2 Then Exit Sub
    If Target.Columns.Count > 1 Then Exit Sub

    If IsEmpty(Target) Then

        Application.EnableEvents = False
        ActiveSheet.Range("X" & Target.Row & ":X" & Target.Row + Target.Rows.Count - 1).ClearContents
        Application.EnableEvents = True
    End If

End Sub

Can someone please help me with this?

Answer 1

You misunderstand the purpose of the function IsEmpty. 

I assume the cells without a value are what you're truly looking for (blank cells). The number of cells with values is displayed in the next line. If it equals 0, they are all left empty.

Target.SpecialCells(xlCellTypeConstants).Count

Yet, the above line of code will result in an error if all cells are empty. Hence, you will have to adjust your code as follows:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column <> 2 Then Exit Sub
If Target.Columns.Count > 1 Then Exit Sub

Dim bolIsEmpty As Boolean

On Error GoTo AllAreEmpty
bolIsEmpty = Target.SpecialCells(xlCellTypeConstants).Count = 0
On Error GoTo 0

If bolIsEmpty Then
    ' ... your code here ...
End If

Exit Sub

AllAreEmpty:
    bolIsEmpty = True
    Resume Next

End Sub