How to clear dependent drop down list cell after selecting changed in Excel?
For a dependent drop down list, when the value of the parent drop down list is changed, the selected value in the second one will be invalid. You have to manually remove the invalid value from the second drop down list after selecting changed in the first one. In this article, I will show you a method to automatically clear the dependent drop down list cell after selecting changed in Excel.
The following VBA code helps you to clear dependent drop down list after selecting changed in Excel.
1. Right click the sheet tab contains the dependent drop down list you will clear automatically, then select View Code from the context menu.
2. In the opening Microsoft Visual Basic for Applications window, copy below VBA code into the window.
VBA code: clear dependent drop down list cell after selecting changed
Private Sub Worksheet_Change(ByVal Target As Range) 'Update by Extendoffice 2018/06/04 Application.EnableEvents = False If Target.Column = 5 And Target.Validation.Type = 3 Then Target.Offset(0, 1).Value = "" End If Application.EnableEvents = True End Sub
Note: In the code, number 5 is the column number contains the parent drop down list. In this case, my parent drop down list locates in column E.
3. Press the Alt + Q keys to close the Microsoft Visual Basic for Applications window.
From now on, when changes made to the parent drop down list, contents of the second drop down list will be cleared automatically. See screenshot:
- How to create dependent cascading drop down lists in Excel?
- How to create dynamic cascading list boxes in Excel?
- How to auto populate other cells when selecting values in Excel drop down list?
- How to autocomplete when typing in Excel drop down list?
- How to create a drop down list calendar in Excel?
Best Office Productivity Tools
Supports Office/Excel 2007-2021 and 365 | Available in 44 Languages | Easy to Uninstall Completely
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need Is Just A Click Away...
Supercharge Your Excel Skills: Experience Efficiency Like Never Before with Kutools for Excel (Full-Featured 30-Day Free Trial)
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day! (Full-Featured 30-Day Free Trial)