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. We’ll see how to clear dependent drop down cells if the first cell changes.

dependentclear02

In this example, a macro will clear out the city cell, if the country changes

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:

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 Dependent Drop Down

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.

Or, if you want to clear dependent drop down lists in multiple cells, use the code in the “Code to Clear Multiple Cells” section

clear dependent drop down

Code to Clear Dependent Drop Down 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

Code to Clear Multiple Cells

In the sample code below, the first drop down is in column B. There are dependent drop downs in columns C and D.

  • The code checks for a data validation drop down list in the changed cell (Validation.Type property is equal to 3)
  • If the changed cell is in column B (2), the cells starting 1 column to the right, and ending 2 columns to the right, have their contents cleared.
  • If the changed cell is in column C (3), 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)
'clear contents of dependent cells
On Error Resume Next
If Target.Validation.Type = 3 Then
  Application.EnableEvents = False
  Select Case Target.Column
    Case 2  'clear columns C and D
      Range(Target.Offset(0, 1), _
        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

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.

______________

Save

Save

You may also like...

12 Responses

  1. PK says:

    why code is not working when I put column number other than 2,3 (e.g. 44,45)

  2. Bernard says:

    You are a genius!!!

  3. Bassem says:

    thx for yr efforts
    it is working normally with me.

  4. Ryan says:

    What if you have a merged cell?
    It all works brilliant with singular cells but not once I merge them, any ideas as to why?

  5. xbobdragonslayer says:

    In my case B24 has the Parent drop down list. H24, 6 columns to the right has the Dependent drop down list. In your code I changed If Target. Column = 3 Then to 6 in the Sheet1 Code. At the top of Sheet1 Code there is a drop down Worksheet and a second drop down to the right Change. I simply closed the Sheet1 Code. My worksheet still does not clear H24 when B24 changes.

    • If the parent is in column B, and cell to be cleared is in H, the code would be:

      If Target.Column = 2 Then
        If Target.Validation.Type = 3 Then
         Application.EnableEvents = False
         Target.Offset(0,6).ClearContents
        End If
      End If
  6. Anthony says:

    Hi Debra,

    My situation is a bit more nuanced, hope you can help! I have a main dropdown selection in a merged area from O6:R7. When I change the selection, I want to clear the contents of two dependent dropdowns located in cells C8 and U8, respectively. Is there a way to accomplish this in VBA?

    Thanks in advance!
    Anthony

  7. David Gray says:

    I was having an issue around the script not running on specific columns. This is the code I came up with to fix that issue:

    Private Sub Worksheet_Change(ByVal Target As Range)
    ‘clear contents of dependent cells
    On Error Resume Next

    If Target.Validation.Type = 3 Then
    Application.EnableEvents = False

    Select Case Target.Column
    Case Range(“j1”).Column ‘clear columns K and L
    Range(Target.Offset(0, 1), Target.Offset(0, 2)).ClearContents
    Case Range(“k1”).Column ‘clear column L
    Target.Offset(0, 1).ClearContents
    End Select
    End If

    exitHandler:
    Application.EnableEvents = True
    Exit Sub

    End Sub

  1. January 2, 2017

    […] then go back and change the country to India, the state would still remain as Florida. Here is a great tutorial by Debra on clearing dependent (conditional) drop down lists in Excel when the selection is […]

  2. February 13, 2017

    […] then go back and change the country to India, the state would still remain as Florida. Here is a great tutorial by Debra on clearing dependent (conditional) drop down lists in Excel when the selection is […]

Leave a Reply

Your email address will not be published. Required fields are marked *