Get Unique list of cells in a Column for loading a Combobox

[ayrus]

Hi,

I have following data in a column (B):

C02/2401

C02/2801

C02/2401

C02/2801

C03/1015

C03/1006

C03/1015

C03/1006

C03/1006

C03/1015

C03/1015

C03/1006

I am wondering if it is possible to remove duplicates in the list and load unique and sorted values to a Combobox placed on a VBA form.

thanks

[mana]

Option Explicit

Sub test()
    Dim c As Range
    Dim s As String
    
    With CreateObject("system.collections.arraylist")
        For Each c In Sheets("sheet1").Columns("B").SpecialCells(xlCellTypeConstants)
            s = c.Value
            If Not .contains(s) Then .Add s
        Next
        .Sort
        ComboBox1.List = .toarray
    End With

End Sub

[ayrus]

Option Explicit

Sub test()
    Dim c As Range
    Dim s As String
    
    With CreateObject("system.collections.arraylist")
        For Each c In Sheets("sheet1").Columns("B").SpecialCells(xlCellTypeConstants)
            s = c.Value
            If Not .contains(s) Then .Add s
        Next
        .Sort
        ComboBox1.List = .toarray
    End With

End Sub

Hello Mana,
Many thanks for your prompt response, I am using following code, wondering if you can advise on that..

Dim myJointCodeRange As Range
Set myJointCodeRange = Application.Range("B:B")

Dim myRow As Range
Dim myVal As Variant

For Each myRow In myJointCodeRange.Cells

myVal = myRow.Value

If Not InStr(myVal, "/") = 0 Then
Me.cmbJointID.AddItem (myVal)
End If

Next

[ayrus]

Hello everyone,
will be thankful for any feedback; here is my existing code..

Dim myJointCodeRange As Range
Set myJointCodeRange = Application.Range("B:B")

Dim myRow As Range
Dim myVal As Variant

For Each myRow In myJointCodeRange.Cells

myVal = myRow.Value

If Not InStr(myVal, "/") = 0 Then
Me.cmbJointID.AddItem (myVal)
End If

Next