return string instead of array

[sylvio]

Please, help me modify the function to get instead of an array a string of items separated by semicolons.
Now by pressing F9 I get ={"item1","item2",...."itemX"} but I need just item1;item2;....;itemX

Function NoDups(rng As Range)
  Dim arr(), i&, s$, x
  arr = Intersect(rng.Parent.UsedRange, rng).Value
  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
          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


    ReDim arr(1 To .Count)
    For i = 1 To .Count
      arr(i) = .Item(i)
    Next
  End With
[COLOR=#ff0000]  ' return array[/COLOR]
  NoDups = arr()
End Function

 

[FormR]

Hi, try changing the last line to:

NoDups = Join(arr(), ";")

 

[sylvio]

Hi, try changing the last line to:

NoDups = Join(arr(), ";")

Perfect!
Now the function returns correct list of items.
But still I cannot use it as a source for dropdown list.
Any suggestion?

 

[FormR]

But still I cannot use it as a source for dropdown list.
Any suggestion?

Hi, looks to me like you were given a suggestion here:

http://www.mrexcel.com/forum/excel-...op-list-unique-sorted-values.html#post4716950

 

[sylvio]

ups, indeed
Thank you!