How to Remove Invalid Names from an Excel Roster Dropdown Using VBA
Question details
The user needs to automatically remove outdated or invalid names from an Excel dropdown list based on an updated roster.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Updating a data validation dropdown list to exclude names that have been removed from a master roster sheet.
- Observed behavior
- Standard Excel formulas cannot dynamically remove already selected invalid names from cells; a VBA macro is required to compare and clear invalid entries.
Before running a VBA macro, ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled the Developer tab in your ribbon.
Use a VBA Macro to Compare and Clear Invalid Names
Applying a VBA macro is the most effective way to automatically check selected names against an updated roster and clear any invalid entries.
Standard Excel formulas cannot automatically clear a cell's value if it no longer matches a dropdown list. A VBA routine iterates through your selected cells, compares them against the current roster, and clears the cell if the name is no longer valid.
Press ALT + F11 in Excel to open the Visual Basic for Applications (VBA) editor.
Click on 'Insert' in the top menu and select 'Module' to create a blank script window.
Write a VBA script that loops through the cells containing your dropdowns, uses the 'Match' function to check if the name exists in your updated roster sheet (e.g., 'eTA Roster'), and clears the cell contents using Selection.ClearContents if no match is found.
Close the VBA editor and press ALT + F8 to open the Macro dialog. Select your newly created macro and click 'Run' to clean up the invalid names.

Create and Manage Dynamic Dropdowns in WPS Office
WPS Spreadsheets provides robust support for Data Validation and VBA macros, making it easy to manage dynamic employee rosters and clean up invalid data seamlessly.
- 1. Enable Macros: Open your workbook in WPS Spreadsheets and go to the 'Developer' tab to enable macro execution.
- 2. Open the VBA Editor: Click on 'Visual Basic' in the Developer ribbon or press ALT + F11 to access the script editor.
- 3. Manage Data Validation: Go to the 'Data' tab and select 'Validation' to set up your roster dropdowns based on named ranges.

Frequently Asked Questions
Can I remove invalid names using just Excel formulas?
No, standard formulas can update the options available in a dropdown list, but they cannot actively clear or delete an invalid name that has already been selected in a cell. This action requires a VBA macro.
Why is my dropdown list still showing old names?
If your dropdown source range is static, old names will not disappear automatically when the roster changes. You need to use a dynamic named range (using OFFSET and COUNTA functions) or format your roster as a Table so the dropdown options update automatically.
How do I handle VBA macro security warnings?
To run your roster cleanup macro, you must enable macros in your Trust Center settings. Go to File > Options > Trust Center > Trust Center Settings > Macro Settings and select 'Disable all macros with notification', then click 'Enable Content' when you open the file.
Does WPS Office support Excel VBA macros?
Yes, WPS Office fully supports VBA macros, allowing you to run your existing Excel roster cleanup scripts without modifying the code.




