Select unique items and sort in combo boxes

  • Re: Select unique items and sort in combo boxes


    [vb]Function SortArray(ByVal Ary As Variant, Optional Asc As Boolean = True)

    Dim i As Long
    Dim j As Long
    Dim Tmp

    If Asc Then
    For i = LBound(Ary) To UBound(Ary)
    For j = i To UBound(Ary)
    If LCase$(Ary(i)) > LCase$(Ary(j)) Then
    Tmp = Ary(i)
    Ary(i) = Ary(j)
    Ary(j) = Tmp
    End If
    Next
    Next
    Else
    For i = LBound(Ary) To UBound(Ary)
    For j = i To UBound(Ary)
    If LCase$(Ary(i)) < LCase$(Ary(j)) Then
    Tmp = Ary(i)
    Ary(i) = Ary(j)
    Ary(j) = Tmp
    End If
    Next
    Next
    End If

    SortArray = Ary

    End Function[/vb]


    and use like


    [vb] Dim v, e, k

    With ws.Range("shipname")
    v = .Value
    End With
    With CreateObject("scripting.dictionary")
    .comparemode = 1
    For Each e In v
    If Not .exists(e) Then .Add e, Nothing
    Next
    If .Count Then
    k = .keys
    Me.ComboBox1.List = SortArray(k)
    End If
    End With[/vb]

  • Re: Select unique items and sort in combo boxes


    Quote from akbarnikain;792441

    I am trying to select unique items and sort from named ranges to display in my combo boxes on a userform.
    I can display the unique items however, I am unable to sort the unique items without changing their original respective rows.


    Please refer to the attached file


    Thank you Krishnakumar it was a big help.
    However, there is one issue...The "date" ComboBox9, Column "I", sorts first "dd" then "mm" and finally the "yyyy"
    Hope you have a solution
    Thanks

  • Re: Select unique items and sort in combo boxes


    [vb]Function SortArray(ByVal Ary As Variant, Optional Asc As Boolean = True)

    Dim i As Long
    Dim j As Long
    Dim Tmp

    If Asc Then
    For i = LBound(Ary) To UBound(Ary)
    For j = i To UBound(Ary)
    If IsNumeric(Ary(j)) Or IsDate(Ary(j)) Then
    If Ary(i) > Ary(j) Then
    Tmp = Ary(i)
    Ary(i) = Ary(j)
    Ary(j) = Tmp
    End If
    ElseIf LCase$(Ary(i)) > LCase$(Ary(j)) Then
    Tmp = Ary(i)
    Ary(i) = Ary(j)
    Ary(j) = Tmp
    End If
    Next
    Next
    Else
    For i = LBound(Ary) To UBound(Ary)
    For j = i To UBound(Ary)
    If IsNumeric(Ary(j)) Or IsDate(Ary(j)) Then
    If Ary(i) < Ary(j) Then
    Tmp = Ary(i)
    Ary(i) = Ary(j)
    Ary(j) = Tmp
    End If
    ElseIf LCase$(Ary(i)) < LCase$(Ary(j)) Then
    Tmp = Ary(i)
    Ary(i) = Ary(j)
    Ary(j) = Tmp
    End If
    Next
    Next
    End If

    SortArray = Ary

    End Function[/vb]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!