ouestque
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?
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.