I am trying to find if a user deletes values in certain cells in column B then cells in same rows in column X are also deleted using worksheet_change function.
When I delete only one cell in column B then IsEmpty(Target) returns true and I am able to clear the same row cell in column X.
However, when select multiple cells in column B and press delete button, the IsEmpty(Target) returns False. Now here Target is range of multiple cells. I just can't find a way to find if a user has deleted range of values in column B at the same time. Any help would be much appreciated.
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
Thanks Uttam
You misunderstand the purpose of the function IsEmpty.
I guess what you are really looking for are cells which do not contain a value (blank cells). The following line will give you the count of cells which contain a value. If that is equal to zero then they are all blank.
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments