Sorted Values ComboBox VBA

[evanmer]

I'm trying to pull all unique values from a specific column (starting in D2) from worksheet "RawData" and push it to a combobox ("ComboBoxManager")and have gotten it thus far (see code below) but i'd also like to have it sorted from A to Z but am having trouble, can anyone help?

Private Sub UserForm_Initialize()
Application.ScreenUpdating = False
Dim LastRow&, cell As Range
With Worksheets("RawData")
On Error Resume Next
.ShowAllData
Err.Clear
LastRow = .Cells(Rows.Count, 2).End(xlUp).Row
.Range("D1:D" & LastRow).AdvancedFilter Action:=xlFilterInPlace, Unique:=True
ComboBoxManager.Clear
For Each cell In .Range("D2:D" & LastRow).SpecialCells(12)
ComboBoxManager.AddItem cell.Value
Next cell
On Error Resume Next
.ShowAllData
Err.Clear
End With
Application.ScreenUpdating = True
        
End Sub

 

[Mike LH]

Hi,

Try this

Private Sub UserForm_Initialize()
Dim ComBoList      As Variant
    Dim ComBoTemp       As Variant
    Dim x           As Long
    Dim j           As Long
Application.ScreenUpdating = False
Dim LastRow&, cell As Range
With Worksheets("RawData")
On Error Resume Next
.ShowAllData
Err.Clear
LastRow = .Cells(Rows.Count, 2).End(xlUp).Row
.Range("D1:D" & LastRow).AdvancedFilter Action:=xlFilterInPlace, Unique:=True
ComboBoxManager.Clear
For Each cell In .Range("D2:D" & LastRow).SpecialCells(12)
ComboBoxManager.AddItem cell.Value
Next cell
On Error Resume Next
.ShowAllData
Err.Clear
End With


    
    ComBoList = Me.ComboBoxManager.List
    
    For x = LBound(ComBoList) To UBound(ComBoList) - 1
        For j = x + 1 To UBound(ComBoList)
            If ComBoList(x, 0) > ComBoList(j, 0) Then
                ComBoTemp = ComBoList(x, 0)
                ComBoList(x, 0) = ComBoList(j, 0)
                ComBoList(j, 0) = ComBoTemp
            End If
        Next j
    Next x
    
    Me.ComboBoxManager.List = ComBoList
Application.ScreenUpdating = True
        
End Sub

 

[evanmer]

Thanks for the help Mike.

I tried the added code and what it seems to do is set the combobox default value to the the first value in my data set after its sorted, however still does not sort the combobox.

 

[evanmer]

Hey Mike - I think I didn't enter it correct the first time, it works now...thank you!!