Where is the list coming from?Is there a way to return unique values only in a data validation list that is frequently being refreshed?
Where is the list coming from?
If it is coming from somewhere on a worksheet, you can use Excel's built-in "Remove Duplicates" functionality to remove the duplicates.
Depending on how this list is being edited, you may even be able to automate that process with VBA.
I am not sure I understand.
How exactly do you use a Data Validation list for sorting purposes?
Have you considered filtering/sorting the list right in the MS Query code, so the data comes across exactly as you need it?
Option Explicit
Sub Uniq_Data_Val()
Dim i%: i = 2
Dim arr
Dim rg As Object
Dim Last_ro%: Last_ro = Cells(Rows.Count, 1).End(3).Row
Set rg = CreateObject("System.Collections.Arraylist")
With rg
Do Until i > Last_ro
If Range("a" & i) <> vbNullString _
And Not .Contains(Range("a" & i).Value) Then
.Add Range("a" & i).Value
End If
i = i + 1
Loop
.Sort
arr = .toarray
arr = Join(arr, ",")
End With
With Range("c1").Validation
.Delete
.Add xlValidateList, Formula1:=arr
End With
End Sub
A | B | C | |
---|---|---|---|
1 | Name | Albert | |
2 | Sami | ||
3 | John | ||
4 | Goerge | ||
5 | Albert | ||
6 | |||
7 | Goerge | ||
8 | Salim | ||
9 | Ali | ||
10 | |||
11 | Kamel |