VBA & Macro Problems

Reset a Dependent Drop-Down List in Excel When the Parent Changes

Steve KSteve K Nov 18, 2026 869 views

Question details

Automatically clear a second-level drop-down selection when the first drop-down changes.

Reset a Dependent Excel Drop-Down When the Parent Selection Changes
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet uses a parent data-validation drop-down and a dependent drop-down whose available choices vary with the parent selection.
Observed behavior
The dependent cell should be cleared when the parent selection changes so the user can choose a value from the updated list.
Before you start

Identify the parent and dependent cells and confirm that the dependent cell already has a data-validation list linked to the parent. Use desktop Excel with macros enabled; Excel for the web cannot run VBA macros.

Solution 1Recommended

Clear the Dependent Cell With a Worksheet Change Macro

Use a worksheet event to clear the old selection as soon as the parent cell is edited.

This example uses B2 as the parent drop-down and C2 as the dependent drop-down. Change both cell references to match your worksheet. Clearing the contents leaves C2's data-validation rule in place.

The event responds to edits to B2, including selections and pasted values. It does not fire when a formula in B2 changes only because of recalculation.

1
Open the worksheet code

In desktop Excel, right-click the sheet tab containing the drop-downs and select View Code.

2
Add the event macro

Paste this code into the worksheet code window: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("B2")) Is Nothing Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False Me.Range("C2").ClearContents CleanUp: Application.EnableEvents = True End Sub

3
Save as a macro-enabled workbook

Select File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm). Reopen the workbook and enable macros if prompted and you trust the file.

4
Test both drop-downs

Choose a value in B2, select an option in C2, and then change B2. Confirm that C2 clears and that its drop-down offers the options for the new parent value.

Clear the Dependent Cell With a Worksheet Change Macro
Check for an existing event: If the worksheet already has a Worksheet_Change procedure, incorporate the clearing logic into that procedure rather than adding a second one. Make sure the dependent cell can be edited if the sheet is protected.
Free Microsoft Office alternative

Work With Spreadsheet Drop-Downs in WPS Office

WPS Office is a free, lightweight alternative with a familiar interface for working with Excel-format spreadsheets. Open your existing files in WPS Spreadsheets for an easy transition, but verify VBA and macro support for your WPS edition before relying on the Excel event code.

Create and edit spreadsheet data-validation listsOpen and edit Microsoft Excel formats such as XLSXUse a familiar spreadsheet interface for an easier transitionWork with a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Will clearing the dependent cell remove its drop-down?

No. The macro uses ClearContents, which removes the selected value but leaves the data-validation rule in place. Check the rule under Data > Data Validation.

Can a formula clear the old dependent selection automatically?

A formula can change the options available in a dependent list, but it does not automatically erase an existing value in the input cell. A VBA event is a typical way to clear that value after a parent edit.

Why does the dependent list still show the wrong options?

Select the dependent cell and open Data > Data Validation. Check that its list source refers to the correct parent cell and that any named ranges or source formulas match the parent values.

Why does the macro not run in Excel for the web?

Excel for the web cannot run VBA macros. Open the workbook in desktop Excel, save it as an XLSM file, and enable macros if you trust the workbook.

Will the macro run when the parent cell changes because of a formula?

No. Worksheet_Change responds to edits, not changes caused only by formula recalculation. A different approach, such as a carefully designed Worksheet_Calculate event, is needed for a formula-driven parent cell.