Friday, July 8, 2016

Creating A Combobox Containing Only Unique Distinct Alphabetically Sorted Values

             Sometimes unique values need to fill the combobox and  need to sort alphabetic..In this way, the processes may be easier.

The cells in Column A were selected  to fill combobox in this example :
For x = 2 To Cells(Rows.Count, 1).End(xlUp).Row  -    "1" in the code indicates Column A.

Also,data is filtered with combobox and  copied to the other pages in our study.

The used codes to fill combobox unique values and to sort alphabetic :

Dim x, a, b As Long, c As Variant
'Unique Records
For x = 2 To Cells(Rows.Count, 1).End(xlUp).Row
If WorksheetFunction.CountIf(Range("A2:A" & x), Cells(x, 1)) = 1 Then
ComboBox1.AddItem Cells(x, 1).Value
End If
Next
'Alphabetic Order
For a = 0 To ComboBox1.ListCount - 1
  For b = a To ComboBox1.ListCount - 1
        If ComboBox1.List(b) < ComboBox1.List(a) Then
c = ComboBox1.List(a)
    ComboBox1.List(a) = ComboBox1.List(b)
    ComboBox1.List(b) = c
       End If
  Next
  Next





1 comment:

  1. it works only for alphatical letters it is not working for numerals.

    kindly advice

    ReplyDelete