Clear Dependent Drop Down Cells

Clear Dependent Drop Down Cells

You can use data validation to create a drop down list on an Excel worksheet. For example, show a list of countries, if you click a cell in column B.

After you’ve set up that first list, you can make another list, in column C, to show the cities for the selected country.

Here is the list of cities, showing only the options for Canada.

dependentclear02

We’ll see how to set this up, and prevent problems, by clearing out the city cell, when necessary

dependentclear07

Set up Dependent Drop Down Lists

When one drop down list is based on a selection in another cell, I call that a dependent drop down. I’ve seen other names for the technique, such as “conditional drop down list”. Whatever you call it, it’s fairly easy to set up, as you can see in the video below.

For written instructions, visit one of these pages on my website:

  • Create Dependent Drop Down Lists
  • Dependent Dropdowns from a Sorted List

    Dependent Lists With INDEX

    Problems with Mismatched Selections

    This technique works nicely, until some troublemaker decides to go back to column B, and select a different country, after selecting a city.

    If they don’t re-select from the city column too, you’ll end up with mismatched countries and cities.

    dependentclear03

    Prevent Mismatches With Data Validation

    To prevent mismatches, you can change the data validation formula in the Country column. Instead of just showing the Country list, the data validation can check column C for an entry first. Then, if there is something in the City column, Excel doesn’t show the Region list.

    For example, use this formula:

    =IF(C2=””,CountryList,INDIRECT(“Nothing”))

    If a City is selected in a row, the Country drop down doesn’t work. You’ll have to clear the City cell, then select a different Country.

    dependentclear04

    Use a Macro to Clear the City Cell

    If you frequently make changes in the first drop down, you might get tired of manually clearing the dependent cell. Life is too short, to be aggravated by little things like that!

    So, if you’re able to use macros in your workbook, you can set up a few lines of code that runs automatically, to clear the city cell, when someone selects a country. When cell B5 is changed in the screen shot below, cell C5 will be cleared.

    dependentclear06

    Code to Clear the Cell

    In the sample code below, the first drop down is in column B, and the code will clear any data in column C, in the same row.

    • The code checks the column number of the cell that was changed, to see if it is in column B (2).
    • If there is a data validation drop down list in that cell, the Validation.Type property is equal to 3
    • In that case, the cell that is 1 column to the right has its contents cleared.

    This code would be pasted onto the sheet’s code module. There are instructions here.

    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Target.Column = 2 Then
      If Target.Validation.Type = 3 Then
       Application.EnableEvents = False
       Target.Offset(0, 1).ClearContents
      End If
    End If
    
    exitHandler:
      Application.EnableEvents = True
      Exit Sub
    
    End Sub

    Download the Sample File

    To download the sample file, go to the Dependent Data Validation page, and go to the Download section.

    In the Download section, look for the file named Clear Dependent Cell After Selecting.

    ______________

22 thoughts on “Clear Dependent Drop Down Cells”

  1. HI,

    i love the marco, and i find it very useful.
    i have a question tho.
    what if i want an additional condition to be met for the cell in Target.Column = 2?

    i’ll explain better… i want to clear the cell in column x with Target.Offset(0, 1).ClearContents
    If Target.Column = 2 AND the cell in Target.Column also meets a condition i have to verify with a vlookup formula in a separate Sheet (vlookup formula result 0)

    Thank you

    1. You can use logical operators in the conditional statement…

      If (Target.Column = 2 AND Target.Value = “xxx”) Then
      Do Things
      End If

  2. Hello, I tried using the macro but it is not working for me. Is there a code that allows the interdependent content in the cells to be erased if the cells are stacked on top of each other (in the same column, different rows)? The examples you give are for when the cells are in the same row, but different columns. I am having trouble modifying the macro. Thank you very much

      1. In this scenario where the cells are both in the same column, do you have to change something on this line of code as well?:
        If Target.Validation.Type = 3 Then

  3. Hi. Thanks for this. May I ask for macro instruction for the following conditions:

    I have a data val list for column 1 as A,B,C….G.
    I also have a data val list for column 2 that applies only when selected value in column 1 is “A”, if anything else, adjacent cell in column 2 should automatically return a constant value “N/A”, not a list.

    Appreciate your help.

    1. First of all, if the only valid entry for Column 1 is “A”, then why not just enter A and not worry about a list in that column? Then you can set a data validation for that cell that only allows A to be entered
      (so ‘Allow:’ would be “List”, ‘Source’ would be “A”, and ‘In-cell dropdown’ and ‘Ignore blank’ would be un-checked).

      Second of all, if you only create a named range for “A”, and not for the other possible entries from Column 1, then there will be no dropdown created for anything but A.

  4. Hello. I have four columns using dependent drop downs.
    Column A-“Select Primary Country” . Based on the user selection in column A, the metro drop down list in column B will show only metros defined for that particular country ( Column B-“Select Primary Metro”). I then have a second set of country/metro columns asking a users to choose a backup or secondary ( Column C-Select Secondary Country, Column D-Select Secondary country. Again, depending upon what a user selects in column C, the drop down list in column D displays only the metros defined for that country. The VBA code works greats on clearing the column B if someone changes country in column A. But now I want to add to the VBA code to say if a uses changes the country in column C, I want column D to clear. I am not sure how to build a nested if statement in VBA. Can you help?

  5. Hello,

    I’m attempting to modify this code to work when looking at data in Column G and updating in Column H. I assumed this would be as simple as changing the code to

    If Target.Column = 7 Then
    If Target.Validation.Type = 8 Then

    But that does not appear to work. Is there something I’m blatantly missing? Or should I be taking an additional step to work outside of Columns B and C?

  6. Would this also work on the following setup:
    – the first drop down is in column B and in row 3
    – the dependent drop down is also in column B but in row 5

    1. The sample code clears a cell that is zero rows down, and 1 column to the right
      Target.Offset(0, 1).ClearContents

      Change the Offset settings, to go down 2 rows, and move 0 columns
      Target.Offset(2, 0).ClearContents

  7. Hi,

    How to modify the code to make it work in case I have two dependent drop down lists, rather than one as shown in the example above?

  8. Could you help with the following I’m having when using this code

    The first dropdown is in C8
    The dependant drop down is in C9

    Setting the offset to (1,0) C8 clears C9 but when selecting C9 it clears C10 which has data I want to keep, is there a way to stop C9 from clearing C10

  9. Hi,

    I found this helpful but have another layer I want to add.
    In column A I have options of a, b or c. I only want to clear the contents of column B, C, D and E if A=a. If b or c are selected, I do not want anything to happen to the other columns.
    Can this be done? If so, could you help me with the code?

    Thanks,
    Erin

  10. Thanks Debra for this explanation, I now understand how to reposition my drop boxes, which is why I was reading the comments.

  11. Brilliant! I have zero knowledge about VBA and I was looking for ready VBA code to use it in work template. I’ve tried few different ones with a bit similar code and they either worked only for one cell or crushed. Your code worked just fine and it’s running smoothly since. Thanks a million!

  12. Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Target.Address = Range(“D2”).Address Then
    Application.EnableEvents = False
    Target.Offset(0, 1).ClearContents
    End If

    exitHandler:
    Application.EnableEvents = True
    Exit Sub
    End Sub

    This code also works fine your Target value is in Range(“D2”).address

  13. I am using a similar VBA code for this same function (resetting a dependent cell), though I need to utilize the spreadsheet online now. Since the code will not work online, I’m trying to translate this code to a TypeScript using the Power Automate feature. I’m not having any luck. Do you have any suggestions?

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.