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

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

This drop-down list contains country names:

Germany is repeated thrice, and Italy twice.

To remove the duplicate values from the drop-down list and keep the unique values only:

Method 1 – Developing a Macro to Keep Values that occur at least once in a Drop Down List

For the list above, the output will be Germany, Italy, France, England.

• Use the 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

• Run the code.

This is the output.

Read More: Data Validation Drop Down List with VBA in Excel

Method 2 – Creating a Macro to Keep Values that occur Exactly Once in a Drop Down List

The output will be: France, England.

• Use the 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

• Run the code.

This is the output.

Read More: VBA to Select Value from Drop Down List in Excel

Method 3 – Developing a UserForm to Keep Unique Values in a Drop Down List

Step 1: Open the UserForm

• Go to Insert > UserForm.

UserForm1 will be displayed.

Step 2: Dragging Tools to the UserForm

• Move your cursor over the Toolbox and drag 3 Labels and 2 ListBoxes.
• Place the Listboxes under Label1 and Label3.
• Drag 1 TextBox and place it under Label2.
• Drag a CommandButton to the bottom right corner.

Step 3: Entering a Code in ListBox1

• Double click ListBox1. A Private Subprocedure called (ListBox1_Click) will open. Enter the following code:

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: Entering the Code in TextBox1

• Double-click TextBox1. A Private Subprocedure (TextBox1_Change) will open. Enter the following code:

Private Sub TextBox1_Change()

On Error GoTo TB1:

ActiveSheet.Range(UserForm1.TextBox1.Text).Select

Exit Sub

TB1:
    x = 21

End Sub

Step 6: Entering the Code in CommandButton1

• Double-click CommandButton1. A Private Subprocedure (CommandButton1_Click) will open. Enter the following code:

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: Entering the Code to Run the UserForm

• Insert a new Module and use the following code:

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)

• Run the Run_UserForm Macro.

The UserForm will be displayed in the worksheet.

• Select the worksheet containing the drop-down list. Here, Sheet3.
• Enter the cell reference of the list in the worksheet. Here, B3.
• Select either At Least Once or Exactly Once. Here, At Least Once.

• Click OK.

This is the output.

Download Practice Workbook

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