Adding Items to a ListBox-Unique Items Only

[jpendegraft]

Yes, another ListBox issue, but of a different sort.

I would like to add only unique items to the listbox. the list I a
working with for example has a row PER "wholesaler" for each "Item
that is carried. A "wholesaler" may carry multiple items. I want t
fill the listbox with the "wholesaler" name only once (not once fo
each item).

the code I have is below. It currently adds the wholesaler each tim
the condition is met (not just once). Thanks in advance for any help.



Sub FilterWholesalers()
Dim Row As Integer
Set testWholesaler = ThisWorkbook.Sheets("lookup on brewery no
refrig")
TopicCount
Application.WorksheetFunction.CountA(testWholesaler.Range("A:A"))

cbWholesaler.Clear

For Row = 1 To TopicCount
If Sheet4.Cells(Row, 4) = cbState.Value Then
cbWholesaler.AddItem testWholesaler.Cells(Row, 3).Value
End If
Next Row

cbWholesaler.ListIndex = 0

CurrentTopic = 1
End Su

 

[Tom Ogilvy]

There are other ways, but using your current code:

Sub FilterWholesalers()
Dim Row As Integer, i as Long, j as Long
Dim bUnique as Boolean
Set testWholesaler = ThisWorkbook.Sheets("lookup on brewery non
refrig")
TopicCount =
Application.WorksheetFunction.CountA(testWholesaler.Range("A:A"))

cbWholesaler.Clear

For Row = 1 To TopicCount
If Sheet4.Cells(Row, 4) = cbState.Value Then
if cbWholesaler.Listcount = 0 then
cbWholesaler.AddItem testWholesaler.Cells(Row, 3).Value
else
bUnique = True
for j = 0 to cbWholesaler.Listcount -1
if cbWholesaler.List(j) = testWholesaler.Cells(row,3).Value then
bUnique = False
exit for
end if
Next
if bUnique then
cbWholesaler.AddItem testWholesaler.Cells(row,3).Value
end if
End If
Next Row

cbWholesaler.ListIndex = 0

CurrentTopic = 1
End Sub

code is untested and may have problems, but represents an approach.

 

[jpendegraft]

Worked like a champ! Thanks