function to populate drop-list with unique and sorted values

[sylvio]

Dear friends,

Below is the function NoDups populating drop-down list in a user form with unique and sorted values.
Can it modified to populate regular drop-down set through Data validation?

Function NoDups(rng As Range)
  Dim arr(), i&, s$, x
  ' read data
  arr = Intersect(rng.Parent.UsedRange, rng).Value
  ' create list
  On Error Resume Next
  With New Collection
    For Each x In arr()
      s = Trim(x)
      If Len(s) > 0 Then
        If IsEmpty(.Item(s)) Then
          ' add sorted values to collection
          For i = 1 To .Count
            If s < .Item(i) Then Exit For
          Next
          If i > .Count Then .Add s, s Else .Add s, s, Before:=i
        End If
      End If
    Next
    ' copy to array
    ReDim arr(1 To .Count)
    For i = 1 To .Count
      arr(i) = .Item(i)
    Next
  End With


  NoDups = arr()
End Function

 

[sylvio]

Dear Vladimir,
thank you.
Somehow I get error 1004. Any suggestion?
And just to be sure, I need to sort unique text values.