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 out 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.

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

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

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

⧭ 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.

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

⧭ 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: How to Create a Drop Down List with Unique Values in Excel (4 Methods)

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

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

⧭ 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.

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

⧭ 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.

Related Content: How to Make Multiple Selection from Drop Down List in Excel (3 Ways)

Similar Readings:

Creating a Drop Down Filter to Extract Data Based on Selection in Excel
How to Create Excel Drop Down List with Color (2 Ways)
Excel Drop Down List Not Working (8 Issues and Solutions)
Auto Update Drop Down List in Excel (3 Ways)
VBA to Select Value from Drop Down List in Excel (2 Methods)

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.

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

⧪ 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.

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

⧪ 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

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

⧪ 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

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

⧪ 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

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

⧪ 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

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

⧪ 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.

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

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:

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

Then click OK. You’ll get the duplicate values removed from the drop-down list of the input location according to your chosen criterion.

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

Read More: How to Make a Drop-Down List Based on Formula in Excel (4 Ways)

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.

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. And don’t forget to visit our site ExcelDemy for more posts and updates.

How to Link a Cell Value with a Drop Down List in Excel (5 Ways)
Conditional Drop Down List in Excel (Create, Sort and Use)
How to Create Dynamic Dependent Drop Down List in Excel
How to use IF Statement to Create a Drop-Down List in Excel
VLOOKUP with Drop Down List in Excel

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

How to Keep Unique Values in a Drop Down List with Excel VBA

1. Developing a Macro to Keep Unique Values in a Drop Down List that Appear at Least Once

2. Creating a Macro to Keep Unique Values in a Drop Down List that Appear Exactly Once

3. Developing a UserForm to Put Unique Values in a Drop Down List

Things to Remember

Conclusion

Related Articles