How to clear content of dependent cell when new selection is made?

[ArunBalaji]

I have created a dependent list which will display selected values based on the parent list.
Now I am looking for a option to clear the contents in dependent list when i change the selection in the parent list.

Ex:
Column K2 to K400 i have the parent list (Transport, Admin, HR)

Column L2 to L400 i have the dependent list (Transport = Van, Cab) (Admin = Lighting, Food) (HR = Compensation, Income Tax)

Scenario - If the current selection in parent is Admin then I will be able to select Van or Cab in the list (Column L2, L3 and so on). Now if i change the selection in Parent (Column K2, K3 and so on) existing value in dependent should be cleared.

Please advise.

 

[Ron Coderre]

Assumptions:
Parent List cells are in K2:K400
Dependent List cells are in L2:L400


• Right-click on the tab name
• Select: View Code
• Copy the below VBA code and paste it into the VBA editor.
Then...Whenever a Parent cell is changed, its corresponding dependent cell will be cleared.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngAllParentCells As Range
Dim rngDepCells As Range
Dim rngCell As Range

Set rngAllParentCells = Range("K2:K400")
Set rngDepCells = Intersect(Target, rngAllParentCells)

If Not rngDepCells Is Nothing Then
   For Each rngCell In rngDepCells.Cells
     'Move 1 cell to the right and clear contents
      rngCell.Offset(RowOffset:=0, ColumnOffset:=1).ClearContents
   Next rngCell
End If

Set rngAllParentCells = Nothing
Set rngDepCells = Nothing
Set rngCell = Nothing
End Sub

Is that something you can work with?

BTW: A good website for Data Validations is:
http://www.contextures.com/xlDataVal01.html

 

[ArunBalaji]

Thank You Ron. This works for me