VBA & Macro Problems

How to Prevent Excel from Removing Data Validation in Macro Workbooks

Bushra Parveen Sep 28, 2026 869 views

Question details

The user needs to prevent Excel from automatically deleting data validation lists in macro-enabled workbooks.

How to Prevent Excel from Removing Data Validation in Macro Workbooks

Product: Microsoft Excel
Device & OS: not provided
Scenario: Working with a macro-enabled workbook (.xlsm) that contains data validation drop-down lists referencing external or internal data ranges.
Observed behavior: Excel removes the data-validation lists upon opening the file when it detects workbook corruption, unsupported content, or issues with externally referenced validation ranges.

Before you start

Before making any structural changes or testing your file, create a backup copy of your macro-enabled workbook (.xlsm) to ensure no VBA code or data is permanently lost during recovery.

Solution 1Recommended

Verify Data Validation Ranges and Remove External Links

Fix broken data validation sources by ensuring all referenced ranges are valid and contained within the local workbook to prevent Excel from flagging them as corrupted.

Excel's automatic repair feature often strips out data validation rules if they point to external files that are missing, corrupted, or unsupported in the current macro environment. Isolating the validation data inside the same workbook usually resolves the issue.

Isolate the validation data

Open your workbook and locate the cells containing data validation. Ensure that the source lists are located on a hidden sheet within the same workbook rather than in an external file.

Check for broken external links

Go to the 'Data' tab on the Excel ribbon and click 'Edit Links' (if available). Review the list for any broken or unreadable external source files and break or update those links.

Test without macros

Save a copy of the file in the standard '.xlsx' format to strip away the macros. Reopen the file to see if the data validation persists. If it does, the issue is likely tied to a specific VBA script interfering with the validation ranges.

Verify Data Validation Ranges and Remove External Links

Important: If you rely on dynamic external references, consider using Power Query to pull the data into a hidden local sheet first, then point your data validation to that local sheet.

Solution 2

Update Microsoft 365 and Submit the File for Investigation

Ensure your software is up to date to rule out known bugs, and submit the corrupted file directly to Microsoft for further analysis.

Free Microsoft Office alternative

Use WPS Office to Safely Edit Macro-Enabled Workbooks

If Microsoft Excel continues to unexpectedly corrupt or remove your data validation lists, try WPS Office. It provides excellent compatibility with .xlsm files, ensuring your advanced formatting, data validation rules, and macros remain intact without aggressive automatic deletions.

1. Download and Install: Get the free version of WPS Office from the official website and install it on your computer.
2. Open Your Workbook: Launch WPS Spreadsheet and open your original .xlsm file. WPS will read the XML structure without aggressively stripping validation rules.
3. Enable Macros and Verify: Enable macros if prompted, and navigate to your data validation cells to confirm the dropdown lists function exactly as intended.

Seamlessly supports Microsoft Excel formats (.xlsx, .xlsm) without altering complex formulasReliably preserves complex data validation rules and dropdown listsLightweight application that runs smoothly even on older hardwareFamiliar spreadsheet interface that requires zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel remove data validation when a file is corrupted?

Excel's file recovery process aggressively strips out unreadable or corrupted XML components so that the workbook can at least be opened. If data validation rules are linked to external files or invalid ranges, Excel often categorizes them as corrupted and deletes them during this process.

Do VBA macros directly cause data validation lists to disappear?

Macros themselves do not cause the lists to disappear unless a specific VBA script is explicitly programmed to clear validation (e.g., using 'Validation.Delete'). However, complex VBA interacting with unstable external data sources can sometimes trigger Excel's corruption detection mechanism upon saving.

How can I protect my data validation rules from being deleted?

Keep your source data within the same workbook (preferably on a hidden sheet), avoid referencing volatile external files or network drives for validation lists, and ensure you are running the latest version of your spreadsheet software.

Excel & Data Problems