VBA Help with sorting CommoBox alphabetically and numerically

[VBAIntern]

I am new to VBA and I apologies for bad coding practice in advance.

I need help to sort out the options from my ComboBox, but do not want it to affect the data from excel (like sorting the data on excel too). I was able to get it to only display unique values from my data with the coding below. I was hoping to sort the options from the ComboBox from a-z and numerically. Thanks!

Private Sub UserForm_Initialize()


    Dim Motors() As Variant
    Dim lastrow As Integer
    Dim Check As Boolean
    Dim a As Integer
    Dim b As Integer
    Dim i As Integer
    Dim j As Integer




'Finding last row
    lastrow = Sheet1.Cells(2, 1).End(xlDown).Row
    L = lastrow - 1
    
    ReDim Motors(L, 28) As Variant
    
    For i = 1 To L Step 1
        For j = 1 To 28 Step 1
            Motors(i, j) = Sheet1.Cells(1 + i, 1 + j)
        Next j
    Next i


For a = 1 To L
            Check = True
        For b = 0 To Me.MotorBrand.ListCount - 1
            If Sheet1.Cells(a + 1, 4).Value = Me.MotorBrand.List(b) Then
                Check = False
                    ElseIf Sheet1.Cells(a + 1, 4).Value = "" Then
                        Check = False
        Exit For
            End If
        Next b
            If Check Then
                Me.MotorBrand.AddItem Sheet1.Cells(a + 1, 4).Value
            End If
        Next a

End Sub

 

[Rick Rothstein]

Where is your ComboBox... on a worksheet or on a UserForm? If on a worksheet, then what kind of ComboBox is it... a Form or ActiveX one?

Am I correct in assuming you only want to load Column D values into your ComboBox?

 

[VBAIntern]

It is on a UserForm, I figured out how to do sort a-z by using the code below. But it does not work to sort numerically. And yes I am getting the data I want to load from a column, but do not want to sort the data on the actual excel file, just in the ComboBox. Thanks.

 For c = 0 To Me.MotorBrand.ListCount - 1
            For d = c To Me.MotorBrand.ListCount - 1
                If Me.MotorBrand.List(d) < Me.MotorBrand.List(c) Then
                    temp = Me.MotorBrand.List(c)
                    Me.MotorBrand.List(c) = Me.MotorBrand.List(d)
                    Me.MotorBrand.List(d) = temp
                End If
            Next d
        Next c

 

[Rick Rothstein]

I am not sure what that last code is supposed to be, but the following code will get the data in Column D of the active worksheet, remove any duplicate values, sort the remainder and then assign those unique sorted values to a ComboBox named MotorBrand...

  Dim X As Long, ColD As Variant, Uniques As Variant, Sorted As Variant
  
[COLOR="#008000"]  ' Get Column D Value As A 2-Dimensional Array
[/COLOR]  ColD = Range("D1", Cells(Rows.Count, "D").End(xlUp))
  
[COLOR="#008000"]  ' Get Unique Values
[/COLOR]  With CreateObject("Scripting.Dictionary")
    For X = LBound(ColD) To UBound(ColD)
      .Item(ColD(X, 1)) = 1
    Next
    Uniques = .Keys
  End With
  
[COLOR="#008000"]  ' Sort The Unique Values
[/COLOR]  With CreateObject("System.Collections.ArrayList")
    For X = LBound(Uniques) To UBound(Uniques)
      .Add Uniques(X)
    Next
    .Sort
    Sorted = .ToArray
  End With
  
[COLOR="#008000"]  ' Put the Sorted Unique List Into The ComboBox
[/COLOR]  MotorBrand.List = Sorted

 

[excelvba_developer]

Combobox can be filled with unique values as ascending order(A-Z) using Ado connection and with following codes :

Set con = CreateObject("adodb.connection")
Sheets("Page1").ComboBox1.Clear
Sheets("Page1").ComboBox2.Clear
    
    #If VBA7 And Win64 Then
con.Open "provider=microsoft.ace.oledb.12.0;data source=" & ThisWorkbook.FullName & ";extended properties=""excel 12.0;hdr=yes"""
    #Else
con.Open "provider=microsoft.jet.oledb.4.0;data source=" & ThisWorkbook.FullName & ";extended properties=""excel 8.0;hdr=yes"""
    #End If
          
Set rs = CreateObject("adodb.recordset")
sorgu = "select Date from [Page1$] group by Date"
rs.Open sorgu, con, 1, 1
While Not rs.EOF


Sheets("Page1").ComboBox1.AddItem VBA.Format(rs("Date").Value, "dd.mm.yyyy")
Sheets("Page1").ComboBox2.AddItem VBA.Format(rs("Date").Value, "dd.mm.yyyy")
rs.movenext
Wend


rs.Close
con.Close

Or it could be with following codes :

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