VBA & Macro Problems

How to Clear a Dependent Excel Drop-Down When the Source Changes

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to automatically clear the value of a dependent drop-down list in column B whenever the source selection in column A is modified.

Product: Excel
Device & OS: not provided
Scenario: Updating a primary drop-down list (like a Yes/No selection) while having a dependent drop-down list that needs resetting to prevent mismatched data.
Observed behavior: The dependent drop-down in column B retains its previous value even after the source value in column A is changed.

Before you start

Before applying VBA code, ensure your primary and dependent drop-down lists are correctly configured using Data Validation, and note the specific cell ranges they occupy.

Solution 1Recommended

Use a Worksheet-Change VBA Macro to Clear the Dependent Cell

This solution uses a VBA macro to automatically monitor the source cells (e.g., column A) and clear the contents of the adjacent dependent cell (e.g., column B) whenever a change occurs.

By default, Excel's Data Validation does not actively monitor or change existing values in other cells when a source cell is modified. Using a Worksheet_Change event ensures that old data is wiped out immediately to prevent inconsistencies.

Open the VBA Editor

Right-click the sheet tab containing your drop-down lists at the bottom of your Excel window and select 'View Code' from the context menu to launch the VBA Editor.

Paste the VBA Macro

In the code window for that specific worksheet, paste the following VBA code: Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Set rng = Intersect(Range("A1:A50"), Target) If Not rng Is Nothing Then Application.EnableEvents = False rng.Offset(0, 1).ClearContents Application.EnableEvents = True End If End Sub

Customize the Source Range

If your primary drop-down list is in a different location, change the 'A1:A50' range in the code to match your actual source cells.

Test the Automation

Close the VBA Editor and test your lists by changing a value in column A. The adjacent cell in column B will now clear automatically.

Save as a Macro-Enabled Workbook

Go to File > Save As, and choose 'Excel Macro-Enabled Workbook (*.xlsm)' from the file format dropdown to ensure your VBA code is saved permanently.

Enable Macros: Users opening this workbook must have macros enabled in their Trust Center settings for the automatic clearing function to work.

Advanced Spreadsheet Automation

Create and Automate Dependent Drop-Downs Easily in WPS Spreadsheet

WPS Spreadsheet offers full compatibility with Excel's Data Validation and VBA macros. You can seamlessly set up dependent drop-down lists and run Worksheet_Change events without missing a beat, all within a lightweight and intuitive interface.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your .xlsm or .xlsx workbook containing the drop-down lists.
2. Access the VBA Editor: Go to the Developer tab on the ribbon and click on 'Visual Basic', or right-click the sheet tab and select 'View Code'.
3. Apply the Macro: Paste the Worksheet_Change VBA code to automatically clear dependent cells when source data is modified.
4. Save as Macro-Enabled: Save your document in the .xlsm format to keep the VBA macros active and functional.

Fully compatible with Microsoft Excel (.xlsx and .xlsm) formats.Advanced Data Validation features for building complex drop-down menus.Built-in VBA editor (in supported versions) to automate tasks like clearing dependent cells.Free, lightweight, and easy-to-use interface for seamless migration.

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't a dependent drop-down clear automatically without VBA?

By default, Excel's Data Validation only restricts what can be entered into a cell moving forward. It does not actively trigger actions to modify or clear existing values in other cells when a source cell is changed.

Can I apply this VBA macro to multiple columns?

Yes, you can modify the Intersect(Range("A1:A50"), Target) portion of the code to include multiple ranges, such as Range("A1:A50, C1:C50"), depending on where your primary drop-downs are located.

What does Application.EnableEvents = False do in the macro?

This line temporarily disables Excel events. It prevents the macro from triggering itself in an endless loop when the script executes the command to clear the dependent cell's contents.

Why is my dependent cell not clearing after pasting the code?

Ensure that macros are enabled in your workbook settings, the file is saved as an .xlsm, and the code is placed in the specific Worksheet module (e.g., Sheet1) rather than a standard Module.

Excel & Data Problems