Sort an Array of items by Alpha

Hi,

I have the following script that collects my data into an array and adds to a combo-box, However the data is unsorted. I found a script that is suppose to sort the array, However I'm not having any luck with it. it may be the way I am calling it or something else I cant see at the moment.

I'm looking to either sort the array then add the items to the combo-box or Just sort the items that are placed in the combo-box. Or maybe someone has another Idea...

Can someone please take a look and advise.

Thanks as always, Mike

Private Sub UserForm_Initialize()
Dim startrow As Long
Dim lastrow As Long
Dim myArray()
Dim a As Long
Dim i As Long
Dim myRowValue As String
Dim myColumn As Long
Dim Temp As Variant, X As Integer
myColumn = 249
startrow = 4
lastrow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
For i = startrow To lastrow
myRowValue = Cells(i, myColumn).Value
    If myRowValue = "0" Or myRowValue = vbNullString Then
    Else
        ReDim Preserve myArray(a)
        myArray(a) = myRowValue
        a = a + 1
    End If
Next
For N = LBound(myArray) To UBound(myArray)
ComboBox4.AddItem myArray(N)
Next
Stop
SortArray (myArray)
 
End Sub

The Sort Script...

Public Function SortArray(ByRef TheArray As Variant)
Sorted = False
Do While Not Sorted
    Sorted = True
For X = 0 To UBound(TheArray) - 1
    If TheArray(X) > TheArray(X + 1) Then
        Temp = TheArray(X + 1)
        TheArray(X + 1) = TheArray(X)
        TheArray(X) = Temp
        Sorted = False
    End If
Next X
Loop
End Function

sub snb()
  cells(1,50).resize(combobox.listcount)=application.transpose(combobox.list)
  columns(50).sort cells(1,50)
  combobox1.list=columns(50).specialcells(2)
  columns(50).clearcontents
End Sub

Don't know if this is any use to you but it's something I use that's similar to what you are trying to achieve but using a collection rather than an array which has the advantage of only accepting unique items as well:

Sub Refresh_List()

    Dim AllCells As Range, Cell As Range
    Dim NoDupes As New Collection
    Dim i As Integer, j As Integer
    Dim Swap1, Swap2, Item
           
    With ActiveSheet
        Set AllCells = .Range("A2:A" & .Cells(Rows.Count, 1).End(xlUp).Row)
    End With
    
'   The next statement ignores the error caused
'   by attempting to add a duplicate key to the collection.
    On Error Resume Next
    For Each Cell In AllCells
        NoDupes.Add Cell.Value, CStr(Cell.Value)
'       Note: the 2nd argument (key) for the Add method must be a string
    Next Cell

'   Resume normal error handling
    On Error GoTo 0
   
'   Sort the collection
    For i = 1 To NoDupes.Count - 1
        For j = i + 1 To NoDupes.Count
            If NoDupes(i) > NoDupes(j) Then
                Swap1 = NoDupes(i)
                Swap2 = NoDupes(j)
                NoDupes.Add Swap1, before:=j
                NoDupes.Add Swap2, before:=i
                NoDupes.Remove i + 1
                NoDupes.Remove j + 1
            End If
        Next j
    Next i
    
'   Add the sorted, non-duplicated items to a ComboBox
    
    For Each Item In NoDupes
        ComboBox4.AddItem Item
    Next Item
    
End Sub

Dom

or:

Sub snbubble()
  With ComboBox1
    .List = Sheets(1).Cells(1).Resize(17).Value
    
    For j = 0 To UBound(.List) - 1
      For jj = j + 1 To UBound(.List)
        If .List(j) > .List(jj) Then
          c01 = .List(j)
          .List(j) = .List(jj)
          .List(jj) = c01
        End If
      Next
    Next
  End With
End Sub

Ok, I gave both ways a try,

@ snb, I was not able to get your codes to work in my situation again I ran into the same problems I did with my sort script. I'm sure it's my fault as I just dont understand what it going on. As you say avoid code you dont understand... just my luck I guess. But thank you for your time...

@ Dom, I was able to get your option to work, Seems to work better anyway rather than to use an array of items. as I can sort then place the list right away.
Thank You for your time...

Mike,

@RNG

I can't believe you did anything wrong
Please do what you would ask any OP: post your example workbook.

cfr. the attachment

Mike,

Here's a 'black box' approach with an example. It does a simple insertion sort.

Sub x()
    With UserForm1
        .Show vbModeless
        .ListBox1.List = Range("A1:A10").Value
        SortTheBox .ListBox1
    End With
End Sub

Function SortTheBox(vCtl As Variant) As Boolean
    Dim i         As Long
    Dim j         As Long

    If Not IsObject(vCtl) Then Exit Function

    Select Case TypeName(vCtl)
        Case "ComboBox", "ListBox"
            With vCtl
                For i = 1 To .ListCount - 1
                    For j = 0 To i - 1
                        If .List(i) < .List(j) Then
                            .AddItem .List(i), j
                            .RemoveItem i + 1
                            Exit For
                        End If
                    Next j
                Next i
            End With

            SortTheBox = True
    End Select
End Function