Link to home
Start Free TrialLog in
Avatar of ouestque
ouestqueFlag for United States of America

asked on

Excel: How to alphatize items in a collection

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

ASKER CERTIFIED SOLUTION
Avatar of cyberkiwi
cyberkiwi
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial