Trying to Sorting the Loaded Column in Listbox from A to Z

Question

I have been trying to sort the Column values from A to Z which are populated in the List Box.

I have tried with the following but it does not adjust it. Any help will be appreciated.

Dim ws As Worksheet
Dim rng As Range
Dim myArray 

Set ws = Sheets("Sheet2")
Set rng = ws.Range("A2:A" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row), Order1:=xlAscending

With Me.ListBox1
 .Clear
 .ColumnHeads = False
 .ColumnCount = rng.Columns.Count
 MyArray = rng
 .List = SortArray(myArray) 


End With

I want to use the Arrays for Sorting Function which will be populated to Listbox.

Sub SortArray(myListBox As MSForms.ListBox, Optional resetMacro As String)


Dim j As Long
Dim i As Long
Dim temp As Variant

If resetMacro <> "" Then
    Run resetMacro, myListBox
End If

With myListBox
    For j = 0 To .ListCount - 2
        For i = 0 To .ListCount - 2
            If LCase(.List(i)) > LCase(.List(i + 1)) Then
                temp = .List(i)
                .List(i) = .List(i + 1)
                .List(i + 1) = temp
            End If
        Next i
    Next j
End With
 
End Sub

Answer 1

Method 1: Sort Data in Cells

You need to sort the range using the Range.Sort method

Set rng = ws.Range("A2:A" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row)
rng.Sort key1:=ws.Range("A2"), order1:=xlAscending, Header:=xlNo

Also see VBA Excel sort range by specific column.

Method 2: Sort Data in Array

Or load the data into an array and sort the array. See VBA array sort function?

Note: The QuickSort algorithm was retrieved from the link above.

Option Explicit

Private Sub LoadButton_Click()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet2")
    
    Dim DataRange As Range
    Set DataRange = ws.Range("A2:A" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row)

    ' 2-dimensional array of the data
    Dim DataArray() As Variant
    DataArray = DataRange.Value
    
    ' Sort data in 2-dimensional array DataArray
    QuickSortArray SortArray:=DataArray, SortColumn:=1
    
    ' Load sorted data into ListBox
    SortedListForm.SortedListBox.List = DataArray
End Sub


' QickSort algorithm that takes a 2-dimensional array
Public Sub QuickSortArray(ByRef SortArray As Variant, Optional ByVal Min As Long = -1, Optional ByVal Max As Long = -1, Optional ByVal SortColumn As Long = 0)
    On Error Resume Next

    'Sort a 2-Dimensional array
    ' SampleUsage: sort arrData by the contents of column 3
    '
    '   QuickSortArray arrData, , , 3
    '
    'Posted by Jim Rech 10/20/98 Excel.Programming
    'Modifications, Nigel Heffernan:
    '       ' Escape failed comparison with empty variant
    '       ' Defensive coding: check inputs

    Dim i As Long
    Dim j As Long
    
    Dim RowTemp As Variant
    Dim ColTempIdx As Long

    If IsEmpty(SortArray) Then
        Exit Sub
    End If
    If InStr(TypeName(SortArray), "()") < 1 Then  'IsArray() is somewhat broken: Look for brackets in the type name
        Exit Sub
    End If
    If Min = -1 Then
        Min = LBound(SortArray, 1)
    End If
    If Max = -1 Then
        Max = UBound(SortArray, 1)
    End If
    If Min >= Max Then    ' no sorting required
        Exit Sub
    End If

    i = Min
    j = Max
    
    Dim SortItem As Variant
    SortItem = Empty
    SortItem = SortArray((Min + Max) \ 2, SortColumn)

    ' We  send 'Empty' and invalid data items to the end of the list:
    If IsObject(SortItem) Then  ' note that we don't check isObject(SortArray(n)) - SortItem *might* pick up a valid default member or property
        i = Max
        j = Min
    ElseIf IsEmpty(SortItem) Then
        i = Max
        j = Min
    ElseIf IsNull(SortItem) Then
        i = Max
        j = Min
    ElseIf SortItem = "" Then
        i = Max
        j = Min
    ElseIf VarType(SortItem) = vbError Then
        i = Max
        j = Min
    ElseIf VarType(SortItem) > 17 Then
        i = Max
        j = Min
    End If

    Do While i <= j
        Do While SortArray(i, SortColumn) < SortItem And i < Max
            i = i + 1
        Loop
        Do While SortItem < SortArray(j, SortColumn) And j > Min
            j = j - 1
        Loop

        If i <= j Then
            ' Swap the rows
            ReDim RowTemp(LBound(SortArray, 2) To UBound(SortArray, 2))
            For ColTempIdx = LBound(SortArray, 2) To UBound(SortArray, 2)
                RowTemp(ColTempIdx) = SortArray(i, ColTempIdx)
                SortArray(i, ColTempIdx) = SortArray(j, ColTempIdx)
                SortArray(j, ColTempIdx) = RowTemp(ColTempIdx)
            Next ColTempIdx
            Erase RowTemp

            i = i + 1
            j = j - 1
        End If
    Loop

    If (Min < j) Then
        QuickSortArray SortArray, Min, j, SortColumn
    End If
    
    If (i < Max) Then
        QuickSortArray SortArray, i, Max, SortColumn
    End If
End Sub