Only unique values to a combobox from multiple columns, vba

[einheri]

Is there a way to collect unique values from several columns (eg E4 to H1500) and put those values into a userform Combobox with no duplicates.

like: E4 F4 G4 H4
row1 Byan PDIV RSI2

Row2 E5 F5 G5 H5
RSI Byan T8 RS

and so on ......

Hope you understand
Thanks
Einheri
[TABLE="width: 237"]
<colgroup><col span="3"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Akuini]

Try this:

Private Sub UserForm_Initialize()
Dim i As Long
Dim va
Dim d As Object

Set d = CreateObject("scripting.dictionary")
va = Sheets("Sheet1").Range("E4:H500")
For i = 1 To UBound(va, 1)
d(va(i, 1)) = Empty
Next
       
       ComboBox1.List = d.keys
End Sub

EDIT: Wait, do you mean you have multi column combobox? If yes then we need different code.

 

[Norie]

Einheri

From the example data you posted what values would end up in the combobox?

 

[einheri]

Thanks for the replay.
No I do only have 1 combobox and from this I want to see unique values from E4:H500 even if the same value shows in different cells
so if the value "Byan" shows up in eg. E7 and in G18 or in H400 I only want one value to be visible in the combobox.
Thanks

 

[einheri]

Thanks for the replay.
Please see my replay to Akuini.

 

[Akuini]

Thanks for the replay.
No I do only have 1 combobox and from this I want to see unique values from E4:H500 even if the same value shows in different cells
so if the value "Byan" shows up in eg. E7 and in G18 or in H400 I only want one value to be visible in the combobox.
Thanks

OK, then please try the code in post #2 .

 

[einheri]

did work but did not pick upp all unique values

 

[einheri]

Akuini. Tried your solution but it not work Only picked values from the first column and skipped the others.

 

[Akuini]

Akuini. Tried your solution but it not work Only picked values from the first column and skipped the others.

Ah, you're right, my mistake.
Try this one:

Private Sub UserForm_Initialize()
Dim i As Long
Dim va, x
Dim d As Object

Set d = CreateObject("scripting.dictionary")
va = Sheets("Sheet1").Range("E4:H500")
For Each x In va
d(x) = Empty
Next
       
       ComboBox1.List = d.keys
End Sub

 

[einheri]

Briljant
You saved me several hours of work!!
Thanks a lot
//Einheri