View Full Version : Get Unique list of cells in a Column for loading a Combobox
ayrus
08-05-2019, 04:20 AM
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
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
08-05-2019, 05:42 AM
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
08-07-2019, 06:00 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.