VBA & Macro Problems

Clear an Excel Cell After Changing Data Validation with VBA

Steven ParkerSteven Parker Nov 18, 2026 868 views

Question details

Learn how to clear a cell after updating its data-validation list with VBA.

Clear an Excel Cell After Changing Data Validation 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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA editor

In Excel, open the Developer tab and select Visual Basic, then open the module containing your macro or insert a new module.

2
Update the validation and clear the cell

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

3
Run and check the macro

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.

Clear the cell after updating its validation list
Clear only when appropriate: ClearContents removes the cell's value or formula but leaves its formatting and validation rule in place. Omit that line if the existing entry should be retained.
Free Microsoft Office alternative

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.

Create and manage spreadsheet data-validation lists.Open and edit Microsoft Excel XLSX files.Use a familiar interface for everyday spreadsheet work.Work with a free, lightweight office suite.
microsoft office alternative - wps office

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.