Skip to main content

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.

Clear dependent drop down list cell after selecting changed with VBA code


Clear dependent drop down list cell after selecting changed with VBA code

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:


Related articles:

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


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!
Comments (9)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Deleting Multiple rows gives error. Any suggestions ?
This comment was minimized by the moderator on the site
I was getting errors with multiple row deletion as well as rows being deleted that shouldn't have been cleared. Below is the solution that worked for me.

Private Sub Worksheet_Change(ByVal Target As Range)
'Update by D 2022/08/23
On Error Resume Next
Application.EnableEvents = False
If Target.Column = 9 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Target.Offset(0, 4).Value = ""
End If
End If

Application.EnableEvents = False
If Target.Column = 9 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Target.Offset(0, 5).Value = ""
End If
End If

Application.EnableEvents = False
If Target.Column = 13 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Target.Offset(0, 1).Value = ""
End If
End If

exitHandler:
Application.EnableEvents = True
Exit Sub

End Sub
This comment was minimized by the moderator on the site
I am trying to have this apply to two dependent drop downs and cannot seem to get the code right. I tried copying the code and updating the offset and using the and function and get an error each time. Any advice? I want the two columns next to the drop down to clear if it is changed instead of just one.
This comment was minimized by the moderator on the site
Hi, this is how I got it working for multiple drop downs, the "And" function didn't work for me either but this seems to. Essentially you need a different "If" statement for each drop down you want to go blank when you change the chosen value in the first drop down menu. There may be a more efficient way to do this but this worked for me!


Private Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 2018/06/04
Application.EnableEvents = False
If Target.Column = 2 And Target.Validation.Type = 3 Then
Target.Offset(0, 1).Value = ""
End If
If Target.Column = 2 And Target.Validation.Type = 3 Then
Target.Offset(0, 2).Value = ""
End If
If Target.Column = 2 And Target.Validation.Type = 3 Then
Target.Offset(0, 3).Value = ""
End If
If Target.Column = 2 And Target.Validation.Type = 3 Then
Target.Offset(0, 4).Value = ""
End If
Application.EnableEvents = True
End Sub
This comment was minimized by the moderator on the site
It does not work.
This comment was minimized by the moderator on the site
Hi Marlborek,
Which Excel version are you using?
This comment was minimized by the moderator on the site
Working Perfectly
This comment was minimized by the moderator on the site
To post as a guest, your comment is unpublished.
This comment was minimized by the moderator on the site
سلام وقت شما بخیر
ما فایل اکسلی داریم که خروجیش از طریق نرم افزار همکاران سیستم هست یعنی فایل اکسل ما آنلاین به data base نرم افزار همکاران متصله(این مهم نیست برای اطلاع عرض کردم) توی این فایل فیلترهایی وجود داره که هر فیلتر یک لیست کشویی داره مشکل ما اینه که وقتی میخواهیم هر کدوم از فیلتر ها یکی از موارد لیست کشویی رو انتخاب کنیم با انتخاب لیست،لیست کشویی زود می پره یعنی بسته میشه زود و نمیشه چیزی رو انتخاب کرد،اینم بگم خدمتتون که آفیس رو حذف و نصب هم کردم بازم جواب نداد یعنی یه مدت خیلی کوتاهی جواب میده بعد به حالت قبل بر میگرده با پشتیبانی همکاران هم تماس گرفتیم گفتن مشکل از آفیستونه
(آفیسمون 2016 هست)یعنی عملا اونها هم نتونستن مشکل رو پیدا کنن.
لطفا اگه راهی هست ممنون میشم راهنماییم کنید.
با تشکر
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations