VBA & Macro Problems

Excel VBA: Clear a Dependent Data Validation Cell When Its Parent Changes

Huma Ashraf ChHuma Ashraf Ch Nov 12, 2026 869 views

Question details

The user needs to clear a dependent data validation selection when the parent cell changes.

Product
Microsoft Excel desktop
Device & OS
not provided
Scenario
Cell B3 contains a dependent data validation list based on cell A3. The user changes A3 and wants B3 cleared automatically if its previous value is no longer valid.
Observed behavior
The previous value remains in B3 after A3 changes, even when that value is not included in the updated dependent list.
Before you start

Confirm that A3 is the parent cell and B3 is the dependent cell, then open the workbook in desktop Excel. Save a copy as an Excel Macro-Enabled Workbook (*.xlsm) before adding the worksheet event code.

Solution 1Recommended

Use a Worksheet_Change event to clear B3 when A3 changes

Use this solution when the dependent list must reset automatically in desktop Excel after the parent selection changes.

The Worksheet_Change event monitors A3 and clears the adjacent cell B3 whenever A3 is edited. Disabling events temporarily prevents the macro from triggering itself while it clears B3.

1
Open the worksheet code window

In desktop Excel, right-click the worksheet tab that contains A3 and B3, then select View Code.

2
Paste the worksheet event code

Paste this code into the worksheet module: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range("A3"), Target) Is Nothing Then Application.EnableEvents = False Range("A3").Offset(0, 1).ClearContents Application.EnableEvents = True End If End Sub

3
Save as a macro-enabled workbook

Select File > Save As, choose Excel Macro-Enabled Workbook (*.xlsm), and save the file.

4
Enable macros and test the list

Close and reopen the workbook if needed, select Enable Content when prompted, change the value in A3, and verify that B3 is cleared.

Compatibility limitation: This worksheet VBA event requires desktop Excel for Windows or Mac. VBA does not run in Excel for the web or in the Excel mobile apps.
Free Microsoft Office alternative

Try WPS Office for Lightweight Spreadsheet and Data Validation Workflows

WPS Office is a free, lightweight alternative for creating and editing spreadsheets with a familiar interface and strong Microsoft Office format compatibility. It can support standard spreadsheet workflows and seamless migration, although this Excel-specific VBA event may require Microsoft Excel desktop.

  1. 1. Download WPS Office: Select Download WPS Office Free and install WPS Office on your computer.
  2. 2. Open the spreadsheet: Launch WPS Spreadsheets and select Open to load your Excel workbook.
  3. 3. Review the data validation setup: Select the dependent cell and use the Data tab to inspect or edit its data validation settings.
Create and edit spreadsheet data validation lists in a familiar interfaceLightweight application for everyday spreadsheet editing and collaborationCompatible with common Microsoft Excel XLSX and XLS formatsSeamlessly migrate standard spreadsheet content from Microsoft Office
microsoft office alternative - wps office

Frequently Asked Questions

Why does the old value remain in B3 after A3 changes?

Data validation controls which values can be entered or selected, but it does not automatically remove an existing value when the source list changes. A Worksheet_Change VBA event is needed to clear B3.

Where should the VBA code be pasted?

Right-click the worksheet tab containing A3 and B3, choose View Code, and paste the code into that worksheet's code module. Do not paste it into a standard Module if you want the event to run automatically for that sheet.

What file type is required for this macro?

Save the workbook as Excel Macro-Enabled Workbook (*.xlsm). If it is saved as a regular .xlsx file, the VBA code will not be retained.

Can this dependent-cell reset macro run in Excel for the web?

No. Worksheet VBA events require desktop Excel for Windows or Mac. Excel for the web and Excel mobile apps do not run this VBA code.

How can I clear a different dependent cell instead of B3?

Change the clear command in the worksheet code. For example, replace Range("A3").Offset(0, 1).ClearContents with Range("B3").ClearContents, or specify another target cell directly.