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

Hi,

I have following data in a column (B):

Quote:

---

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

Code:

---

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

---

Quote:

---

Originally Posted by mana

Code:

---

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..

Quote:

---

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

---

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

Quote:

---

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

---