Dynamic/dependent data validation lists - reset second cell if first cell changed

Hi all and thankyou in advance,

I have a sheet with drop-down list validations in cells A1 and B1 (reading off named lists from another sheet).
The contents of B1 is dependent on what is selected in A1.

For example:
- if in A1 "Cities" is selected, then the drop-down in B1 is a list of different cities.
- if in A1 "Countries" is selected, then B1 is a list of different countries.

I want to make sure that B1 reverts to blank if A1 is changed. I also want to be able to insert a row underneath Row 1 and have the validations copy down.

So far I've managed to make B1 go blank whenever A1 is changed using the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim strName As String
    If Target.Cells.Count > 1 Then Exit Sub
        On Error Resume Next
        strName = Target.Name.Name
        On Error GoTo 0
        
    If strName = "Pick_a_City" Then
       Application.EnableEvents = False
       Range("Corresponding_List") = vbNullString
       Application.EnableEvents = True
    End If
End Sub

In the above, "Pick_a_City" is a named range referring to cell A1 and "Corresponding_List" refers to cell B1.

The problem is that this code relies on named ranges so that when a new row is inserted, the drop-down boxes copy (to A2 and B2) but B2 does not go blank if A2 is changed.

Is there a way to do what the above code is doing without needing to refer to named ranges. Maybe something to do with activecells and offsets? I've been battling this for a while but I'm new to VBA and haven't been able to crack it.

I've attached a simplified file as an example. I'm using Excel 2007.

Any help greatly appreciated.

Thanks
G

Hello,

Please wrap all code in code tags. The forum rules explain why and how. I'll do it for you this time, but this time only.

So far I've managed to make B1 go blank whenever A1 is changed using the following code:

I don't think your code does that at all. At least not in my trials.

Your code to insert a new row already duplicates the data validation formulas, so all you need is to clear cell Bx when cell Ax is changed. Code to do this could go like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A:A")) Is Nothing Then
    On Error Resume Next
    Application.EnableEvents = False
    Target.Offset(0, 1).ClearContents
    Application.EnableEvents = True
End If
End Sub

cheers

The only thing I would add to Teylyn's great improvement to your macro is to not abort if you changed multiple cells at once in column A:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell as Range
Application.EnableEvents = False

For Each cell in Target
    If Not Intersect(cell, Range("A:A")) Is Nothing Then _
        cell.Offset(0, 1).ClearContents
Next cell

Application.EnableEvents = True
End Sub

Edit: just posted utter nonsense. Time to call it a day.

thanks for the improvement, Jerry.

test test test

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.