Sorting a Combobox A-Z?

[termeric]

Hello, is there a way to sort a combo alphabetically? I'm not sure if this needs to be done before the box is populated or after.

Right now, I am building a list of unique values in ComboBox2 based on the selection of ComboBox1. The last thing I add is an "All Markets" option, which ends up showing up at the end. I'd like to alphabetize both so that "All Markets" is the first thing on the list, and so that the location on the list are in order instead of just as they occur in the source list.

Private Sub combobox1_Change()
Dim rngCompany As Range
Dim rngList As Range
Dim strSelected As String
Dim LastRow As Long
Dim resultIndex As Integer
Dim i As Long
Dim strFound As Boolean
Dim myString As String

        
        resultIndex = -1
        
        ComboBox2.Clear
   
        ' check that a company has been selected
    If ComboBox1.ListIndex <> -1 Then
    
         strSelected = ComboBox1.Value
        
         LastRow = Worksheets("NB-LastYear").Range("E" & Rows.Count).End(xlUp).Row
        
         Set rngList = Worksheets("NB-LastYear").Range("E2:E" & LastRow)
    
          
    
    
        For Each rngCompany In rngList
        
             If rngCompany.Value = strSelected Then
            
                myString = rngCompany.Offset(, 2)
            
                strFound = False
                
                With Me.ComboBox2
                    '.AddItem ("All Markets")
                     'Loop through combobox
                    For i = 0 To .ListCount - 1
                        If .List(i) = myString Then
                            strFound = True
                            Exit For
                        End If
                    Next i
                     'Check if we should add item
                    If Not strFound Then .AddItem rngCompany.Offset(, 2)
                End With
            
                    'ComboBox2.AddItem rngCompany.Offset(, 2)

            End If
        
        Next rngCompany
        
    
    
        Me.ComboBox2.AddItem ("All Markets")

        Me.ComboBox2.Value = "All Markets"
    
    End If
 
End Sub

 

[Fluff]

How about


Private Sub combobox1_Change()
Dim rngCompany As Range
Dim rngList As Range
Dim strSelected As String
Dim LastRow As Long
Dim resultIndex As Integer
Dim i As Long
Dim strFound As Boolean
Dim myString As String
Dim Lst As Object

Set Lst = CreateObject("system.collections.arraylist")
        
        resultIndex = -1
        
        ComboBox2.Clear
   
        ' check that a company has been selected
    If ComboBox1.ListIndex <> -1 Then
    
         strSelected = ComboBox1.Value
        
         LastRow = Worksheets("NB-LastYear").Range("E" & Rows.count).End(xlUp).Row
        
         Set rngList = Worksheets("NB-LastYear").Range("E2:E" & LastRow)
    
          
    
    
        For Each rngCompany In rngList
        
             If rngCompany.Value = strSelected Then
            
                myString = rngCompany.Offset(, 2)
            
                strFound = False
                
                With Me.ComboBox2
                    '.AddItem ("All Markets")
                     'Loop through combobox
                    For i = 0 To .ListCount - 1
                        If .List(i) = myString Then
                            strFound = True
                            Exit For
                        End If
                    Next i
                     'Check if we should add item
                    If Not strFound Then Lst.Add CStr(rngCompany.Offset(, 2))
                End With
            
                    'ComboBox2.AddItem rngCompany.Offset(, 2)

            End If
        
        Next rngCompany
        
    
    
       Lst.Add "All Markets"
       Lst.Sort
       Me.ComboBox2.List = Lst.toarray
        Me.ComboBox2.Value = "All Markets"
    
    End If
 
End Sub

 

[termeric]

That does sort the values into alphabetical order, but it no longer adds only unique records into the combo box. I have a column of data where its 5-10 unique locations repeated a few hundred times each which is populating combobox2. should I move the sorting, or the adding of the "All Markets" u[p earlier in the code?

 

[Fluff]

How about

Private Sub combobox1_Change()
Dim rngCompany As Range
Dim rngList As Range
Dim strSelected As String
Dim LastRow As Long
Dim resultIndex As Integer
Dim i As Long
Dim strFound As Boolean
Dim myString As String
Dim Lst As Object

Set Lst = CreateObject("system.collections.arraylist")
        
        resultIndex = -1
        
        ComboBox2.Clear
   
        ' check that a company has been selected
    If ComboBox1.ListIndex <> -1 Then
    
         strSelected = ComboBox1.Value
        
         LastRow = Worksheets("NB-LastYear").Range("E" & Rows.count).End(xlUp).Row
        
         Set rngList = Worksheets("NB-LastYear").Range("E2:E" & LastRow)
    
          
    
    
        For Each rngCompany In rngList
        
             If rngCompany.Value = strSelected Then
            
                   If Not Lst.contains(CStr(rngCompany.Offset(, 2))) Then Lst.Add CStr(rngCompany.Offset(, 2))

            End If
        
        Next rngCompany
        
    
    
       Lst.Add "All Markets"
       Lst.Sort
       Me.ComboBox2.List = Lst.toarray
       Me.ComboBox2.Value = "All Markets"
    
    End If
 
End Sub

 

[termeric]

thank you for the help, its much appreciated

 

[Fluff]

My pleasure & thanks for the feedback