I have three dropdown lists. The second and third list are dependent on whatever is selected in the first list.
First list is in range W2:X2 (merged)
Second list is in range Y2:Z2 (merged)
Third list is in range AA2:AC2 (merged)
these cells are merged for readability because cells in lower rows are less wide
now I have below code to clear the contents of list 2 and 3 when the first list selection is changed. Unfortunately it is not clearing, probably because I'm using merged cells. I already tried to use range(W2:Y2").offset(,2).clearcontents but this doesn't work either. Can anyone tell me what I'm doing wrong?
First list is in range W2:X2 (merged)
Second list is in range Y2:Z2 (merged)
Third list is in range AA2:AC2 (merged)
these cells are merged for readability because cells in lower rows are less wide
now I have below code to clear the contents of list 2 and 3 when the first list selection is changed. Unfortunately it is not clearing, probably because I'm using merged cells. I already tried to use range(W2:Y2").offset(,2).clearcontents but this doesn't work either. Can anyone tell me what I'm doing wrong?
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$W$2" Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Target.Offset(, 2).ClearContents
Target.Offset(, 3).ClearContents
End If
exitHandler:
Application.EnableEvents = True
Exit Sub
End If
End Sub