Clear an Excel Cell After Changing Data Validation with VBA
Question details
Learn how to clear a cell after updating its data-validation list with VBA.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A VBA macro changes the data-validation list for cell E4, and the user wants to remove its previous entry.
- Observed behavior
- Changing the validation rule leaves the cell's existing value in place.
Confirm that E4 is the intended cell and that CTRYS_MOV_ANO is a valid named range for the new list. Use Excel's VBA editor to add or update the macro, and save the workbook in a macro-enabled format such as .xlsm.
Clear the cell after updating its validation list
Use ClearContents when the old entry should be removed after the validation rule changes.
Updating a validation rule changes the allowed input list but does not automatically replace or remove the cell's current value.
In Excel, open the Developer tab and select Visual Basic, then open the module containing your macro or insert a new module.
Add the following code inside your macro, adjusting the cell address or named range if needed: With Range("E4") .Validation.Delete .Validation.Add Type:=xlValidateList, Formula1:="=CTRYS_MOV_ANO" .ClearContents End With
Run the macro from Developer > Macros. Select E4 and open its drop-down to check the new list; the previous cell entry should be gone.

Manage Spreadsheet Drop-Down Lists in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Microsoft Office format compatibility for an easy transition. You can manage data-validation lists in WPS Spreadsheets, though switching apps does not fix an Excel-specific VBA issue.

Frequently Asked Questions
Why does the old value remain after I change the validation list?
Excel updates the cell's validation rule but does not automatically change its existing value. Add .ClearContents after .Validation.Add if you want to remove the old entry.
Does ClearContents remove the drop-down list?
No. Range("E4").ClearContents removes the cell's value or formula, but it does not remove its data-validation rule or formatting.
How can I check the named range used by the list?
In Excel, open Formulas > Name Manager and look for CTRYS_MOV_ANO. Check that it refers to the intended list and is accessible from the worksheet where the validation is applied.
How do I keep the VBA macro when saving the workbook?
Use File > Save As and choose Excel Macro-Enabled Workbook (.xlsm). When reopening the file, enable macros only if you trust the workbook.




