DropBox getting dropdown menu from filtered table

[alopecito]

Hi all,

I came with this issue:
I have a userform getting the data from a table, the problem is that I have duplicate items on the column A, I get the drop-down from A menu, this is code I'm using:



Dim cell As Range
Dim Rng As Range



With ThisWorkbook.Sheets("sheet1")
Set Rng = .Range("A2", .Range("A2").End(xlDown))
End With

For Each cell In Rng.Cells
Me.ComboBox4.AddItem cell.Value
Next cell





The values are in column A and I filtered as per column B - I only showing the units FIRM, the dropdown code brings ALL the values.

Sample


Unit #
Status


101
Firm


101
Cancelled


102
Firm


103
Firm


104
Firm


104
Cancelled


105
Firm


106
Firm


107
Firm


107
Cancelled


108
Firm


109
Firm



Ideas?

Thanks!

[austenr]

Having no idea what you are doing or what the goal is. With what you posted 101 appears 2 times with a unique status. is that not want to see? You could use unique values in your drop down list but not knowing which one you would want to omit, no one is likely to help you without more information. Your code is doing exactly what you want it to.

[Bob Phillips]

Dim coll As Collection
Dim itm

Set coll = New Collection
For Each itm In ActiveSheet.ListObjects("Table1").DataBodyRange.Rows

On Error Resume Next
coll.Add itm.Cells(1, 2).Value, itm.Cells(1, 2).Value
On Error GoTo 0
Next itm

For Each itm In coll

Me.ComboBox1.AddItem coll.Item(itm)
Next itm