In this article, I’ll show you how you can remove the duplicate values from a drop down list of a worksheet in Excel with the help of VBA and keep only the unique values. You’ll learn to extract the unique values that appear both at least once and exactly once.
Unique Values in a Drop Down List with Excel VBA (Quick View)
Sub Drop_Down_List_Unique_Values_At_Least_Once()
List_Location = "B3"
Data = Range(List_Location).Validation.Formula1
Data = Split(Data, ",")
Range(List_Location).Validation.Delete
Unique_Data = ""
Count = 0
For i = LBound(Data) To UBound(Data)
   Unique_Values = Split(Unique_Data, ",")
   For j = LBound(Unique_Values) To UBound(Unique_Values)
       If Data(i) = Unique_Values(j) Then
           Count = 1
           Exit For
       End If
   Next j
   If Count = 0 Then
       If Unique_Data = "" Then
           Unique_Data = Unique_Data + Data(i)
       Else
           Unique_Data = Unique_Data + "," + Data(i)
       End If
   End If
   Count = 0
Next i
Range(List_Location).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:=Unique_Data
End Sub
How to Keep Unique Values in a Drop Down List with Excel VBA
Here we’ve got a drop-down list in cell B3 of an Excel worksheet that contains the names of some countries.
But as you can see, in the list, some of the names have been repeated. Like Germany has been repeated thrice, and Italy has been repeated twice.
Our objective today is to remove the duplicate values from the drop-down list and keep the unique values only.
1. Developing a Macro to Keep Unique Values in a Drop Down List that Appear at Least Once
First of all, we’ll develop a Macro to keep the unique values that appear at least once in the drop-down list.
For example, for the list mentioned above, the output of the Macro will be Germany, Italy, France, England.
The VBA code for this purpose will be:
⧠VBA Code:
Sub Drop_Down_List_Unique_Values_At_Least_Once()
List_Location = "B3"
Data = Range(List_Location).Validation.Formula1
Data = Split(Data, ",")
Range(List_Location).Validation.Delete
Unique_Data = ""
Count = 0
For i = LBound(Data) To UBound(Data)
   Unique_Values = Split(Unique_Data, ",")
   For j = LBound(Unique_Values) To UBound(Unique_Values)
       If Data(i) = Unique_Values(j) Then
           Count = 1
           Exit For
       End If
   Next j
   If Count = 0 Then
       If Unique_Data = "" Then
           Unique_Data = Unique_Data + Data(i)
       Else
           Unique_Data = Unique_Data + "," + Data(i)
       End If
   End If
   Count = 0
Next i
Range(List_Location).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:=Unique_Data
End Sub
⧠Output:
Run the code. It’ll remove the duplicate values from the drop-down list of cell B3 of the active worksheet and keep only the values that appear at least once.
⧠Notes:
Don’t forget to activate the worksheet with the drop-down list before running the code. Also, change the cell reference of the list location according to your need before running the code.
Read More: Data Validation Drop Down List with VBA in Excel
2. Creating a Macro to Keep Unique Values in a Drop Down List that Appear Exactly Once
This time, we’ll develop a Macro to keep the unique values that appear exactly once in the drop-down list.
For example, for the list mentioned above, the output of the Macro will be France, England.
The VBA code for this purpose will be:
⧠VBA Code:
Sub Drop_Down_List_Unique_Values_Exactly_Once()
List_Location = "B3"
Data = Range(List_Location).Validation.Formula1
Data = Split(Data, ",")
Unique_Data = ""
Range(List_Location).Validation.Delete
Count = 0
For i = LBound(Data) To UBound(Data)
   For j = LBound(Data) To UBound(Data)
       If j <> i And Data(i) = Data(j) Then
           Count = 1
           Exit For
       End If
   Next j
   If Count = 0 Then
       If Unique_Data = "" Then
           Unique_Data = Unique_Data + Data(i)
       Else
           Unique_Data = Unique_Data + "," + Data(i)
       End If
   End If
   Count = 0
Next i
Range(List_Location).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:=Unique_Data
End Sub
⧠Output:
Run the code. It’ll remove the values that have duplicates from the drop-down list of cell B3 of the active worksheet and keep only the values that appear exactly once.
⧠Notes:
Again don’t forget to activate the worksheet with the drop-down list before running the code. Also, change the cell reference of the list location according to your need before running the code.
Read More: How to Use Named Range for Data Validation List with VBA in Excel
3. Developing a UserForm to Put Unique Values in a Drop Down List
Finally, we’ll develop a UserForm to remove the duplicate values from a drop-down list and keep only the unique values with VBA.
⧪ Step 1: Opening the UserForm
Go to the Insert > UserForm option in the VBA editor to open a new UserForm. A new UserForm called UserForm1 will be opened.
⧪ Step 2: Dragging Tools to the UserForm
Besides the UserForm, you’ll get the Toolbox. Move your cursor over the Toolbox and drag 3 Labels and 2 ListBoxes (Under Label1 and Label3) and 1 TextBox (Under Label2) in a way shown in the figure.
Finally, drag a CommandButton to the bottom right corner.
⧪ Step 3: Writing Code for ListBox1
Double click on ListBox1. A Private Subprocedure called ListBox1_Click will open. Enter the following code there.
Private Sub ListBox1_Click()
For i = 0 To UserForm1.ListBox1.ListCount - 1
   If UserForm1.ListBox1.Selected(i) = True Then
       Worksheets(UserForm1.ListBox1.List(i)).Activate
       Exit For
   End If
Next i
End Sub
⧪ Step 4: Writing Code for TextBox1
Then double click on TextBox1. Another Private Subprocedure called TextBox1_Change will open. Enter the following code there.
Private Sub TextBox1_Change()
On Error GoTo TB1:
ActiveSheet.Range(UserForm1.TextBox1.Text).Select
Exit Sub
TB1:
   x = 21
End Sub
⧪ Step 6: Writing Code for CommandButton1
Finally, double click on CommandButton1. A Private Subprocedure called CommandButton1_Click will open. Enter the following code there.
Private Sub CommandButton1_Click()
List_Location = UserForm1.TextBox1.Text
Data = Range(List_Location).Validation.Formula1
Data = Split(Data, ",")
Range(List_Location).Validation.Delete
Unique_Data = ""
Count = 0
If UserForm1.ListBox2.Selected(0) = True Then
   For i = LBound(Data) To UBound(Data)
       Unique_Values = Split(Unique_Data, ",")
       For j = LBound(Unique_Values) To UBound(Unique_Values)
           If Data(i) = Unique_Values(j) Then
               Count = 1
               Exit For
           End If
       Next j
       If Count = 0 Then
           If Unique_Data = "" Then
               Unique_Data = Unique_Data + Data(i)
           Else
               Unique_Data = Unique_Data + "," + Data(i)
           End If
       End If
       Count = 0
   Next i
ElseIf UserForm1.ListBox2.Selected(1) = True Then
   For i = LBound(Data) To UBound(Data)
       For j = LBound(Data) To UBound(Data)
           If j <> i And Data(i) = Data(j) Then
               Count = 1
               Exit For
           End If
       Next j
       If Count = 0 Then
           If Unique_Data = "" Then
               Unique_Data = Unique_Data + Data(i)
           Else
               Unique_Data = Unique_Data + "," + Data(i)
           End If
       End If
       Count = 0
   Next i
Else
   MsgBox "Select Either At Least Once or Exactly Once.", vbExclamation
End If
Range(List_Location).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:=Unique_Data
End Sub
⧪ Step 7: Writing Code for Running the UserForm
Insert a new Module from the VBA toolbar and insert the following code there.
Sub Run_UserForm()
UserForm1.Caption = "Keep Unique Values in Drop-Down List"
UserForm1.Label1.Caption = "Worksheet: "
UserForm1.Label2.Caption = "List Location: "
UserForm1.Label3.Caption = "Keep Unique Values that Appear: "
UserForm1.ListBox1.BorderStyle = fmBorderStyleSingle
UserForm1.ListBox1.ListStyle = fmListStyleOption
For i = 1 To Sheets.Count
   UserForm1.ListBox1.AddItem Sheets(i).Name
Next i
For i = 0 To UserForm1.ListBox1.ListCount - 1
   If UserForm1.ListBox1.List(i) = ActiveSheet.Name Then
       UserForm1.ListBox1.Selected(i) = True
       Exit For
   End If
Next i
UserForm1.ListBox2.BorderStyle = fmBorderStyleSingle
UserForm1.ListBox2.ListStyle = fmListStyleOption
UserForm1.ListBox2.AddItem "At Least Once"
UserForm1.ListBox2.AddItem "Exactly Once"
UserForm1.CommandButton1.Caption = "OK"
Load UserForm1
UserForm1.Show
End Sub
⧪ Step 8: Running the UserForm (The Final Output)
Your UserForm is now ready to use. Run the Macro called Run_UserForm.
The UserForm will be loaded in the worksheet.
Select the worksheet on which the drop-down list lies. Here it’s Sheet3.
Then enter the cell reference of the location of the list on the worksheet. Here it’s B3.
Finally, select either At Least Once or Exactly Once. Here I’ve selected At Least Once.
So my UserForm looks like this:
Then click OK. You’ll get the duplicate values removed from the drop-down list of the input location according to your chosen criterion.
Things to Remember
- In this article, I’ve focused on removing the duplicate values from a drop-down list only. If you want to learn how to create a drop-down list, or how to sort the values in a duplicate list, you can read this article.
Download Practice Workbook
Conclusion
So, these are the ways to remove the duplicate values from a drop-down list and keep only the unique values behind with the help of Excel VBA. Do you have any questions? Feel free to ask us.
Related Articles
- VBA to Select Value from Drop Down List in Excel
- Default Value in Data Validation List with Excel VBA
- Excel VBA to Create Data Validation List from Array
- How to Create Dynamic Drop Down List Using VBA in Excel
- How to Make a Dynamic Data Validation List Using VBA in Excel
- How to Make Multiple Dependent Drop Down List with Excel VBA
