Excel VBA: Clear a Dependent Data Validation Cell When Its Parent Changes
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.
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.
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.
In desktop Excel, right-click the worksheet tab that contains A3 and B3, then select View 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
Select File > Save As, choose Excel Macro-Enabled Workbook (*.xlsm), and save the file.
Close and reopen the workbook if needed, select Enable Content when prompted, change the value in A3, and verify that B3 is cleared.
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. Download WPS Office: Select Download WPS Office Free and install WPS Office on your computer.
- 2. Open the spreadsheet: Launch WPS Spreadsheets and select Open to load your Excel workbook.
- 3. Review the data validation setup: Select the dependent cell and use the Data tab to inspect or edit its data validation settings.

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.




