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