VBD clearning dependent dropdowns for entire sheet

[Kjhersh]

Hi, if someone could help, I'd greatly appreciate it. I've been working on this all week, and I can't find any solutions that fit my problem. I am using the following code and it works perfectly for Row 3 (first row of data entry). This sheet will go on forever as a living document so I need the formula to continue for each row, so I assume I need something after the If Target.Column = 3 And Target.Row = 3 Then

Ideas? Below is the code I'm using. I need dependent dropdown boxes to clear when a selection to a parent box is changed.

Private Sub Worksheet_Change(ByVal Target As Range)
' If Target cell is C3 then...
If Target.Column = 3 And Target.Row = 3 Then
' Clear contents of K3
Target.Offset(0, 8).ClearContents
' Clear contents of L3
Target.Offset(0, 9).ClearContents
' Clear contents of P3
Target.Offset(0, 13).ClearContents
' Clear contents of Q3
Target.Offset(0, 14).ClearContents
End If
' If Target cell is K3 then...
If Target.Column = 11 And Target.Row = 3 Then
' Clear contents of L3
Target.Offset(0, 1).ClearContents
' Clear contents of P3
Target.Offset(0, 4).ClearContents
' Clear contents of Q3
Target.Offset(0, 5).ClearContents
End If
' If Target cell is P3 then...
If Target.Column = 16 And Target.Row = 3 Then
' Clear contents of Q3
Target.Offset(0, 1).ClearContents
End If

Thanks in advance for any ideas!

 

[Norie]

If you want the code to run for every row in the specified columns remove the And Target.Row = 3 criteria from the If statements.

 

[Fluff]

Hi & welcome to MrExcel.
How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Intersect(Target, Range("C:C,K:K,P:P")) Is Nothing Then Exit Sub
   Application.EnableEvents = False
   If Target.Column = 3 Then
      Intersect(Target.EntireRow, Range("K:L,P:Q")).ClearContents
   ElseIf Target.Column = 11 Then
      Intersect(Target.EntireRow, Range("L:L,P:Q")).ClearContents
   ElseIf Target.Column = 16 Then
      Target.Offset(, 1).ClearContents
   End If
   Application.EnableEvents = True
End Sub

 

[Kjhersh]

Hi & welcome to MrExcel.
How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Intersect(Target, Range("C:C,K:K,P:P")) Is Nothing Then Exit Sub
   Application.EnableEvents = False
   If Target.Column = 3 Then
      Intersect(Target.EntireRow, Range("K:L,P:Q")).ClearContents
   ElseIf Target.Column = 11 Then
      Intersect(Target.EntireRow, Range("L:L,P:Q")).ClearContents
   ElseIf Target.Column = 16 Then
      Target.Offset(, 1).ClearContents
   End If
   Application.EnableEvents = True
End Sub

This is PERFECT! Thank you for your quick reply, what an awesome group - I have so much to learn!

 

[Kjhersh]

Thank you!

 

[Fluff]

Glad we could help & thanks for the feedback