VBA to clear value of Dependent drop downs when selection changes in parent drop down

[Latha]

[FONT=&quot]hi, i have the below code to clear value of Dependent drop downs (C13:C22) when selection changes in parent drop downs(B12:B22) but i have an issue. i have rows with some data above B13 and when there is a change in the any of cell between B1 to B12 the corresponding column values are also becoming blank. Please guide me how do i restrict tthe below code to make dependent list blank for cells C13:C22 only.[/FONT]
[FONT=&quot]Private Sub Worksheet_Change(ByVal Target As Range)[/FONT]
[FONT=&quot]‘clear contents of dependent cells

Dim sht As Worksheet
Set sht = ThisWorkbook.Sheets(“Billing”)[/FONT]

[FONT=&quot]On Error Resume Next
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Select Case Target.Column[/FONT]
[FONT=&quot]Case 2 ‘clear columns C and D
Target.Offset(0, 1).ClearContents
‘ Target.Offset(0, 2)).ClearContents
‘ Case 3 ‘clear column D
‘ Target.Offset(0, 1).ClearContents
End Select
End If[/FONT]
[FONT=&quot]exitHandler:
Application.EnableEvents = True
Exit Sub[/FONT]
[FONT=&quot]End Sub

[/FONT]

 

[AFPathfinder]

I think if you just add a second condition to the If statement, that should take care of the issue.

If Target.Validation.Type = 3 [COLOR=#0000ff]And Target.Row > 12 [/COLOR]Then

Altogether:

Private Sub Worksheet_Change(ByVal Target As Range)
'clear contents of dependent cells
    Dim sht As Worksheet
    Set sht = ThisWorkbook.Sheets(“Billing”)
    
    On Error Resume Next
    If Target.Validation.Type = 3 [COLOR=#0000ff]And Target.Row > 12 [/COLOR]Then
        Application.EnableEvents = False
        Select Case Target.Column
        Case 2 'clear columns C and D
            Target.Offset(0, 1).ClearContents
            ' Target.Offset(0, 2)).ClearContents
        ' Case 3 ‘clear column D
            ' Target.Offset(0, 1).ClearContents
        End Select
    End If
exitHandler:
    Application.EnableEvents = True
    Exit Sub
End Sub

 

[Latha]

Thanks. but its still changing values for rows 8 to 11

I think if you just add a second condition to the If statement, that should take care of the issue.

If Target.Validation.Type = 3 [COLOR=#0000ff]And Target.Row > 12 [/COLOR]Then

Altogether:

Private Sub Worksheet_Change(ByVal Target As Range)
'clear contents of dependent cells
    Dim sht As Worksheet
    Set sht = ThisWorkbook.Sheets(“Billing”)
    
    On Error Resume Next
    If Target.Validation.Type = 3 [COLOR=#0000ff]And Target.Row > 12 [/COLOR]Then
        Application.EnableEvents = False
        Select Case Target.Column
        Case 2 'clear columns C and D
            Target.Offset(0, 1).ClearContents
            ' Target.Offset(0, 2)).ClearContents
        ' Case 3 ‘clear column D
            ' Target.Offset(0, 1).ClearContents
        End Select
    End If
exitHandler:
    Application.EnableEvents = True
    Exit Sub
End Sub

 

[Latha]

i fixed this by removing "Target.Validation.Type = 3" from IF condition and its working fine now. Thanks a lot for the help

Private Sub Worksheet_Change(ByVal Target As Range)
'clear contents of dependent cells
    Dim sht As Worksheet
    Set sht = ThisWorkbook.Sheets(“Billing”)
    
    On Error Resume Next
    If Target.Row > 12 Then
        Application.EnableEvents = False
        Select Case Target.Column
        Case 2 'clear columns C and D
            Target.Offset(0, 1).ClearContents
            ' Target.Offset(0, 2)).ClearContents
        ' Case 3 ‘clear column D
            ' Target.Offset(0, 1).ClearContents
        End Select
    End If
exitHandler:
    Application.EnableEvents = True
    Exit Sub
End Sub