Need help loading Combo box and List box without Empty data or duplicates

[jdr360]

I just can't get anything to work, I have the following code which works great, but I have been trying to modify it to add more functionality to it. Like sorting, and duplicate removal. I think I got this code from this site, it's been a while since I had to work on this part of my userform I really do not remember.

Sub FillCombobox(WSName As String, ColLtr As String, CBox As ComboBox)


Dim LastRow As Long
Dim aCell As Range


Set ws = ActiveWorkbook.Worksheets(WSName)
    With ws
        LastRow = .Cells(.Rows.Count, ColLtr).End(xlUp).Row
        For Each aCell In .Range(ColLtr & "2:" & ColLtr & LastRow)
            If Not aCell.Value = "" Then
                CBox.AddItem aCell.Value
            End If
        Next
    End With
    
End Sub

It's a very simple and easy way to populate a combo box without empties, I really like this kinda of reusable code programming, its very smart and simple. I would like to add the ability to remove duplicates and possibly a sort feature. I have searched everywhere and unable to get anything to work. Like I said, the above code works as is removing blanks, but I need something more. I have tried the Dictionary list way and an array, but just not having luck getting anything working. The above code is in the module1 and works great. I call it with

Call FillCombobox("Bill To", "D", Me.cmbBillTo)

I am still learning VBA as I go. Thank you
James

 

[MickG]

Try something like this for data in sheet "Mysheet", column "A" to fill "ComboBox1".

[COLOR="Navy"]Sub[/COLOR] MG08Dec30
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, AL [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]With[/COLOR] Sheets("MySheet")
        [COLOR="Navy"]Set[/COLOR] Rng = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
    [COLOR="Navy"]Set[/COLOR] AL = CreateObject("System.Collections.ArrayList")
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
      [COLOR="Navy"]If[/COLOR] Not IsEmpty(Dn.Value) [COLOR="Navy"]Then[/COLOR]
          [COLOR="Navy"]If[/COLOR] Not AL.Contains(Dn.Value) [COLOR="Navy"]Then[/COLOR] AL.Add Dn.Value
       [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
    
    AL.Sort
[COLOR="Navy"]With[/COLOR] ComboBox1
    .Clear
    .List = AL.ToArray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick