How Create A Dropdown List With Data Validation That Ignores Duplicate Data

The following is an example of VBA code to create a dropdown list with data validation that ignores duplicate data:

Sub createDropdownList()
Dim ws As Worksheet
Dim rangeData As Range
Dim list As Object
Set ws = ActiveSheet 'active worksheet
'initialize list object
Set list = CreateObject("System.Collections.ArrayList")
'get the data range that will be used for the dropdown list
Set rangeData = ws.Range("A1:A10")
'Iterate over all the data and add it to the list if it doesn't already exist
For Each cell In rangeData
If Not list.Contains(cell.Value) Then
list.Add cell.Value
End If
Next
'Create a dropdown list in cell B1
With ws.Range("B1").Validation
.Delete 'remove previous validation if any
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:=Join(list.ToArray, ",")
.IgnoreBlank = True
.InCellDropdown = True
EndWith
End Sub


Some things to do:

1. Initialize an ArrayList object to store unique data
2. Take the source data range, iterate and add it to ArrayList if it doesn't already exist
3. Create a dropdown list in the destination cell, fill it with a formula which is a combination of items in the ArrayList
4. Set validation properties such as IgnoreBlank = True and InCellDropdown = True
This way the dropdown list will only contain unique data from the source range, ignoring duplicate data.