Sub Liste2()
Dim i%, v$, dPlg, oCel As Range, oPlg As Range, Coll As New Collection
Set oPlg = Range("B5:B19") 'plage de données à définir à sa convenance.
dPlg = oPlg.Value
With oPlg.Parent.Sort
.SortFields.Clear
.SortFields.Add Key:=oPlg.Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange oPlg
.Header = xlNo 'à adapter
.MatchCase = False
.Orientation = xlTopToBottom
.Apply
End With
On Error Resume Next
For Each oCel In oPlg.Cells
Coll.Add oCel.Value, CStr(oCel.Value)
Next
oPlg.Value = dPlg
Set oPlg = Nothing
Erase dPlg
On Error GoTo 0
For i = 1 To Coll.Count
v = v & Coll.Item(i) & ","
Next
With [B1].Validation 'ou autre adresse...
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Left$(v, Len(v) - 1)
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub