We help IT Professionals succeed at work.

Excel: How to alphatize items in a collection

ouestque
ouestque asked
on
I was given code that creates a collection and puts that collection into a form in my Excel spreadsheet. See below...

How do I take my collection (After it has been created, and re-create it in alphabetical order?


Private Sub UserForm_Initialize()
Dim r As Range
Dim MaxRow As Long
Dim sName As String
Dim c As New Collection
Dim n As Long

ListBox1.Clear

MaxRow = Range("A" & Cells.Rows.Count).End(xlUp).Row
    sName = ActiveCell.Value

Set r = Cells(1, 1)
    On Error Resume Next
    Do Until r.Row > MaxRow
        If r.Value = sName Then
            c.Add r.Offset(0, 2).Value, r.Offset(0, 2).Value
        End If
        Set r = r.Offset(1, 0)
    Loop
            
For n = 1 To c.Count
    ListBox1.AddItem c(n)
Next n

End Sub

Open in new window

Comment
Watch Question

Expert of the Quarter 2010
Expert of the Year 2010
Commented:
Give this a try.

Regards
Private Sub UserForm_Initialize()
Dim r As Range
Dim MaxRow As Long
Dim sName As String
Dim c As New Collection
Dim n As Long

ListBox1.Clear

MaxRow = Range("A" & Cells.Rows.Count).End(xlUp).Row
    sName = ActiveCell.Value

Set r = Cells(1, 1)
    On Error Resume Next
    Do Until r.Row > MaxRow
        If r.Value = sName Then
            c.Add r.Offset(0, 2).Value, r.Offset(0, 2).Value
        End If
        Set r = r.Offset(1, 0)
    Loop
            
    'Steal code from John Walkenbach’s Excel Power Programming
    'with VBA to sort the array
    
    Dim vTemp
    For i = 1 To c.Count - 1
        For j = i + 1 To c.Count
            If c(i) > c(j) Then
                vTemp = c(i)
                c(i) = c(j)
                c(j) = vTemp
            End If
        Next j
    Next i
            
For n = 1 To c.Count
    ListBox1.AddItem c(n)
Next n

End Sub

Open in new window