Hi!
So, what I'm trying to accomplish is to pull values from a column and store them in a userform-based combobox without any duplicates showing.
So far, I can accomplish pulling all of the values, including duplicates into the box:
[VBA]Private Sub UserForm_Initialize()
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim rnData As Range
Dim vaData As Variant
Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets("Scoring")
With wsSheet
Set rnData = .Range(.Range("C2"), .Range("C65536").End(xlUp))
End With
vaData = rnData.Value
With Me.TeamDrop
.Clear
.List = vaData
.ListIndex = -1
End With
End Sub[/VBA]
I tried searching up methods to remove duplicates while loading everything into the combobox, although couldn't find anything other than only retrieving 'unique values' upon execution.
I've attempted using this code:
[VBA]Sub RemoveDuplicates()
Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection
Dim i As Integer, j As Integer
Dim Swap1, Swap2, Item
Set AllCells = Range("C2:C100")
On Error Resume Next
For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)
Next Cell
On Error GoTo 0
For i = 1 To NoDupes.Count - 1
For j = i + 1 To NoDupes.Count
If NoDupes(i) > NoDupes(j) Then
Swap1 = NoDupes(i)
Swap2 = NoDupes(j)
NoDupes.Add Swap1, before:=j
NoDupes.Add Swap2, before:=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1
End If
Next j
Next i
For Each Item In NoDupes
If Item <> "" Then
frmTeam.TeamBox.AddItem Item
End If
Next Item
End Sub
[/VBA]
although the column-range's values are not listed in the combobox.
Is there something I'm missing?
Thank you in advance!