VBA & Macro Problems

Remove Items from an Excel Multi-Select Drop-Down with VBA

Chanuka GeekiyanageChanuka Geekiyanage Nov 14, 2026 869 views

Question details

Remove an item from an Excel multi-select drop-down cell or prevent duplicate selections.

Remove Items from an Excel Multi-Select Drop-Down with VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet uses VBA to add multiple data validation list selections to one cell.
Observed behavior
Selecting an existing item can add a duplicate instead of removing it.
Before you start

Make sure the target cells already have a Data Validation list and identify their range. Use a desktop version of Excel that supports VBA, and save the workbook as an Excel Macro-Enabled Workbook (.xlsm).

Solution 1Recommended

Toggle Excel Drop-Down Items with a Worksheet Change Macro

Use this macro to append a new selection or remove one matching item when it is selected again.

The example applies to cells B2:B100 and separates items with a comma and a space. Replace that range with your own, and make sure individual list items do not contain commas.

Replace any existing Worksheet_Change procedure in the same worksheet module or integrate the logic into it; a worksheet cannot have two procedures with that name.

1
Open the worksheet module

In Excel, right-click the tab containing the drop-down cells and select View Code.

2
Add the toggle macro

Paste this code into the worksheet module, replacing B2:B100 with your drop-down range: Private Sub Worksheet_Change(ByVal Target As Range) Dim newItem As String, oldValue As String Dim parts As Variant, part As Variant Dim result As String, removed As Boolean On Error GoTo SafeExit If Target.CountLarge <> 1 Then Exit Sub If Intersect(Target, Me.Range("B2:B100")) Is Nothing Then Exit Sub If Target.Validation.Type <> 3 Then Exit Sub If IsError(Target.Value) Then Exit Sub newItem = Trim$(CStr(Target.Value)) If newItem = "" Then Exit Sub Application.EnableEvents = False Application.Undo If IsError(Target.Value) Then GoTo SafeExit oldValue = CStr(Target.Value) If oldValue = "" Then Target.Value = newItem GoTo SafeExit End If parts = Split(oldValue, ",") For Each part In parts If Not removed And StrComp(Trim$(CStr(part)), newItem, vbTextCompare) = 0 Then removed = True ElseIf Trim$(CStr(part)) <> "" Then If result <> "" Then result = result & ", " result = result & Trim$(CStr(part)) End If Next part If Not removed Then If result <> "" Then result = result & ", " result = result & newItem End If Target.Value = result SafeExit: Application.EnableEvents = True End Sub

3
Save and test

Save the file as .xlsm, enable macros if prompted, and select two items from a drop-down in the configured range. Select one of them again to remove it.

Toggle Excel Drop-Down Items with a Worksheet Change Macro
Macro limitations: The macro uses Excel's Undo action to retrieve the previous cell value, which affects the undo history. It handles single-cell changes in the specified range; selecting an item again removes one matching occurrence if duplicates already exist.
Free Microsoft Office alternative

Work with Excel Spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for common Excel formats, making it easy to move everyday spreadsheet work between applications. It does not fix Excel's VBA behavior, and macro support may vary by WPS version.

Work with spreadsheet drop-down lists in a familiar interfaceOpen and edit common Microsoft Excel formats, including XLSX and XLSUse a free, lightweight office suite for everyday spreadsheet tasks
microsoft office alternative - wps office

Frequently Asked Questions

Why does my drop-down add duplicate items?

The existing Worksheet_Change macro may append every selection without checking the cell's current items. Open the worksheet tab's View Code window and add a matching check before appending.

Where should I paste the VBA code?

Right-click the worksheet tab that contains the drop-downs, select View Code, and paste the Worksheet_Change procedure into that worksheet's code window.

Can I use this macro in Excel for the web?

Excel for the web cannot run VBA macros. Open the .xlsm workbook in a desktop version of Excel with VBA support to use and test the macro.

What if my list items contain commas?

The example splits existing selections at commas, so commas inside an item would be treated as separators. Change the delimiter in both Split and the joining logic to a character that does not appear in your list items.

Will selecting a repeated item remove every duplicate?

No. This example removes one matching occurrence each time you select that item again. Repeat the selection if the cell already contains multiple copies.