validation list grouping entries?

[MrPez]

Does anyone know if it is possible to group common entries in validation lists?
I have a named range that has many entries, but one will appear frequently and I was after a way of only displaying it once in the drop-down list.
Thanks!

 

[MickG]

Hi, If you Validation LIST is in column "A" and you Validation CELL is "D1"
Then this code will Fill Column "Z" with Unique Values from column "A" and use it as new list for you Validaton Cell "D1".
Change ranges and cells in code to suit.

[COLOR="Navy"]Sub[/COLOR] MG11Mar15
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    .Item(Dn.value) = Dn.value
[COLOR="Navy"]Next[/COLOR]
    Range("Z1").Resize(.Count) = Application.Transpose(.Items)
    [COLOR="Navy"]Set[/COLOR] Rng = Range("Z1").Resize(.Count)
[COLOR="Navy"]End[/COLOR] With
Range("D1").Select
    [COLOR="Navy"]With[/COLOR] Selection.Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:="=" & Rng.Address & "", AlertStyle:=xlValidAlertStop
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick