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

Get FREE Advanced Excel Exercises with Solutions!

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

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

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.

### 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

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.

### 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

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
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.CommandButton1.Caption = "OK"

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.

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

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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

Advanced Excel Exercises with Solutions PDF