VBA & Macro Problems

How to Remove Invalid Names from an Excel Roster Dropdown Using VBA

Camila MilosovichCamila Milosovich Nov 10, 2026 871 views

Question details

The user needs to automatically remove outdated or invalid names from an Excel dropdown list based on an updated roster.

How to Remove Invalid Names from an Excel Roster Dropdown Using VBA
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 you start

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.

Solution 1Recommended

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.

1
Open the VBA Editor

Press ALT + F11 in Excel to open the Visual Basic for Applications (VBA) editor.

2
Insert a New Module

Click on 'Insert' in the top menu and select 'Module' to create a blank script window.

3
Write the Comparison Routine

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.

4
Run the Macro

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.

Use a VBA Macro to Compare and Clear Invalid Names
Sample Workbook Recommended: Since VBA requires exact sheet names and cell ranges, testing your code on a sample workbook with dummy data is highly recommended before applying it to your live roster.
Manage Rosters efficiently with WPS

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. 1. Enable Macros: Open your workbook in WPS Spreadsheets and go to the 'Developer' tab to enable macro execution.
  2. 2. Open the VBA Editor: Click on 'Visual Basic' in the Developer ribbon or press ALT + F11 to access the script editor.
  3. 3. Manage Data Validation: Go to the 'Data' tab and select 'Validation' to set up your roster dropdowns based on named ranges.
Fully compatible with Microsoft Excel (.xlsx and .xlsm) formats.Built-in VBA support for automating repetitive data cleanup tasks.Advanced Data Validation tools for managing dynamic dropdown lists.Lightweight, fast, and free to use.
microsoft office alternative - wps office

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.