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

VBA Code to Keep Unique Values in Excel VBA Drop Down List


Download Practice Workbook


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.

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

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

VBA Code to Keep Unique Values in Excel VBA Drop Down List

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

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

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

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


Similar Readings:


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.

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

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

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

⧪ 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

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

⧪ 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

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

⧪ 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

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

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

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

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


Related Articles

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo