Omitting Duplicate values using a ComboBox or Data Validation
 

Is it possible to have a ComboBox or a Data Validation list that will omit
duplicate values in the given range?

Regards,
Paul

--

Omitting Duplicate values using a ComboBox or Data Validation
 

On Oct 3, 8:17*pm, "PCLIVE" wrote:
Is it possible to have a ComboBox or a Data Validation list that will omit
duplicate values in the given range?

Regards,
Paul

--

In a sheet module

Function UNIQUE(r As Range)
Dim v, a, z
a = r.Value
With CreateObject("scripting.dictionary")
.comparemode = vbTextCompare
For Each v In a
If Not IsEmpty(v) Then
If Not .exists(v) Then .Add v, Nothing
End If
Next
If .Count 0 Then UNIQUE = .keys
End With
End Function
Private Sub Worksheet_Activate()
With Me.ComboBox1
.Clear
.List = Application.Transpose(UNIQUE(Range("A1", Range("a" &
Rows.Count).End(xlUp))))
End With
End Sub

HTH