Remove Duplicates in Excel Multi-Select Dropdowns with Line Breaks
Question details
The user needs to prevent and remove duplicate selections in a VBA-powered multi-select data validation dropdown where selected items are separated by line breaks instead of commas.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Appending multiple items to a single cell using a data validation dropdown via VBA, and ensuring no duplicate items are added.
- Observed behavior
- Existing duplicate-removal code works for comma-separated values but fails to identify and remove duplicates when items are appended using line breaks (vbNewLine).
Ensure that your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled macros in your Excel Trust Center settings.
Update VBA Delimiter Constants to vbLf
Modify your existing Worksheet_Change event macro to split, evaluate, and rejoin the cell's string using vbLf instead of a comma.
When Excel inserts an in-cell line break, it uses a Line Feed character. If your VBA code is looking for commas to split the text and check for duplicates, it will fail to read line breaks. Changing the delimiter to vbLf ensures the macro correctly isolates each item on its respective line.
Press Alt + F11 to open the Visual Basic for Applications editor. In the Project Explorer, double-click the specific Worksheet (e.g., Sheet1) where your data validation dropdown is located.
Find the Worksheet_Change event macro. Ensure your code is appending new selections with a line break, for example: Target.Value = OldValue & vbNewLine & NewValue.
Locate the portion of your code that splits the cell value into an array to check for duplicates. Replace the comma delimiter (",") with vbLf. For example, change Split(cellValue, ", ") to Split(cellValue, vbLf).
Update the logic that writes the cleaned string back to the cell. Ensure it uses vbLf to join the unique array elements together so they remain on separate lines.
Manage Multi-Select Dropdowns Using WPS Spreadsheets
WPS Spreadsheets provides a robust built-in Macro Editor that fully supports VBA. You can effortlessly implement data validation and multi-select dropdown macros using the exact same code logic as Microsoft Excel.
- 1. Open the Macro Editor: Launch WPS Spreadsheets, go to the Tools tab on the ribbon, and click Developer to open the Macro Editor.
- 2. Paste Your VBA Code: Double-click the target Sheet in the Project window and paste your Worksheet_Change event code, using vbLf as the delimiter.
- 3. Save as Macro-Enabled: Go to Menu > Save As and choose 'Macro-Enabled Workbook (.xlsm)' to preserve your multi-select dropdown functionality.
Frequently Asked Questions
Why should I use vbLf instead of vbNewLine for splitting?
While vbNewLine (which contains both a carriage return and a line feed) works fine for appending text, an actual in-cell line break generated by Excel is strictly a line feed character. Using vbLf guarantees the Split function perfectly isolates the items.
How do I make the VBA code automatically wrap text?
You can force the cell to wrap text directly within your VBA macro by adding the line `Target.WrapText = True` immediately after the code updates the cell's value.
Will this code work if my dropdown items already contain commas?
Yes! One of the main advantages of using line breaks (vbLf) as your delimiter instead of commas is that it allows your individual dropdown items to safely contain commas without breaking the duplicate-removal logic.
