Clear Certain Drop-Downs

[nymyth]

Hey all,

I have 5 dropdowns in column A (A8, A11, A14, A17, A20)

I want to be able to clear 14,17,20 only (20 is dependent on 17, 17 on 14). So if I have a selection in these cells and I change cell A17, I want A20 to clear out. If I change A14 I want A17 and A20 to clear out.

I have the following macro in the sheet:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'clear contents of dependent cells
On Error Resume Next
If Target.Validation.Type = 3 Then
  Application.EnableEvents = False
  Select Case Target.Column = 1
    Case 2  'clear columns C and D
      Range(Target.Offset(3, 0), _
        Target.Offset(6, 0)).ClearContents
    Case 3  'clear column D
      Target.Offset(3, 0).ClearContents
  End Select
End If


exitHandler:
  Application.EnableEvents = True
  Exit Sub


End Sub

However, this isn't working entirely as if I clear out A8 that clears out other cells I don't want touched. I know this has to do with the Target.Column but I cannot figure out how to get this to only start on A14 and down. Any help is appreciated. Thanks.

 

[nymyth]

morning all, sorry to bump this, but still having issues. Can you target a specific cell to start the offset from using target?

 

[nymyth]

Figured it out, I ended up using target.address and made the formula simpler:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$14" Then
        Range("A17").Value = "Please Select..."
        Range("A20").Value = "Please Select..."
    End If


End Sub