Many of the tutorials online for adding unique items from a list to a combobox involve copying unique values from a range to a separate area on the spreadsheet, and then loading those into a combobox.
Instead, you can write code to scroll through the list and add items to the combobox, while tracking what you have already added already to prevent duplication. How? Append values added to the combobox to a string, and before adding another value, check to see if it is already in the string.
Private Sub Userform_Initialize()
'declare variables
Dim UniqueString As String
'activate Sheet1 and select A1
Worksheets("Sheet1").Activate
ActiveSheet.[A1].Select
'scroll down column A until the first empty cell
'if the active cell's value is not in UniqueString
'then append it to UniqueString and add it to ComboBox1
Do Until IsEmpty(ActiveCell.Value)
If InStr(1, UniqueString, " " & ActiveCell.Value & " ") = 0 Then
UniqueString = " " & UniqueString & ActiveCell.Value & " "
ComboBox1.Additem ActiveCell.Value
End If
ActiveCell.Offset(1,0).Select
Loop
End Sub
2 Comments
Dim rng As Range, c As Range, r As Range
Dim rws As Long, y As Integer
rws = ActiveSheet.UsedRange.Columns(1).Rows.Count
ComboBox1.Clear
Set r = Range(Cells(1, 1), Cells(rws, 1))
For Each c In r.Cells
y = Application.WorksheetFunction.CountIf(Range(Cells(1, 1), Cells(c.Row, 1)), c)
If y = 1 Then ComboBox1.AddItem c
Next c
‘———————–No selecting cells required———-
davesexcel.com
This is the another option for you
Sub Button1_Click()
Dim i As Integer
Dim j As Integer
Dim Duplicate As Integer
Range(“A1″).Select
For i = 1 To ActiveCell.CurrentRegion.Rows.Count
Duplicate = 0
For j = i + 1 To ActiveCell.CurrentRegion.Rows.Count
If Cells(i, 1).Value = Cells(j, 1).Value Then
Duplicate = Duplicate + 1
End If
Next j
If Duplicate = 0 Then
UserForm1.ComboBox1.AddItem Cells(i, 1).Value
End If
Next i
UserForm1.Show
End Sub