Unique Values in a Drop Down List with VBA in Excel (A Complete Guide)

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: VBA to Select Value from Drop Down List 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.

Read More: Default Value in Data Validation List with Excel VBA

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

• How to Use Named Range for Data Validation List with VBA in Excel
• 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