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]

You're welcome, glad to help, & thanks for the feedback.

 

[Khanzaki]

Hi everyone !
I was looking for something nearly similar ; but with multiple comboboxes
Was hoping for a help with this one !

I have a large data in Column D ; E ; H ; L
I wish to have list of Unique Items from these Columns into Combobox1 ; combobox2 ; 3 and 4 respectively

this to happen on userform initialize event
Thanks for reading

 

[Akuini]

I have a large data in Column D ; E ; H ; L
I wish to have list of Unique Items from these Columns into Combobox1 ; combobox2 ; 3 and 4 respectively

this to happen on userform initialize event
Thanks for reading

Try this:

Private Sub UserForm_Initialize()
'adjust combobox reference
to_List ComboBox1, "D"
to_List ComboBox2, "E"
to_List ComboBox3, "H"
to_List ComboBox4, "L"

End Sub

Sub to_List(obj As Object, h As String)
Dim d As Object, va, x
Set d = CreateObject("scripting.dictionary")

    With Sheets("Sheet1")  'adjust sheet reference
        va = .Range(.Cells(2, h), .Cells(.Rows.Count, h).End(xlUp)).Value  'data start at row 2
    End With

    For Each x In va
        d(x) = ""
    Next
    If d.Exists("") Then d.Remove "" 'remove blank
    obj.List = d.keys

End Sub

 

[Khanzaki]

Thankyou @Akuini
I tried it ; it gives application defined or object defined error
here is my actual code

Private Sub UserForm_Initialize()
'adjust combobox reference
to_List rec1, "A"
to_List rec3, "C"
to_List rec4, "D"
to_List rec8, "H"

End Sub

Sub to_List(obj As Object, h As String)
Dim d As Object, va, x
Set d = CreateObject("scripting.dictionary")

    With Sheets("Client")  'adjust sheet reference
        va = .Range(.Cells(2, A), .Cells(.Rows.Count, A).End(xlUp)).Value  'data start at row 2
    End With

    For Each x In va
        d(x) = ""
    Next
    If d.Exists("") Then d.Remove "" 'remove blank
    obj.List = d.keys

End Sub

where am i going wrong ?

I dont know how to attach a sample workbook here

 

[Khanzaki]

Private Sub UserForm_Initialize()
'adjust combobox reference
to_List rec1, "A"
to_List rec3, "C"
to_List rec4, "D"
to_List rec8, "H"

End Sub

Sub to_List(obj As Object, h As String)
Dim d As Object, va, x
Set d = CreateObject("scripting.dictionary")

    With Sheets("OPD")  'adjust sheet reference
        va = .Range(.Cells(2, h), .Cells(.Rows.Count, h).End(xlUp)).Value  'data start at row 2
    End With

    For Each x In va
        d(x) = ""
    Next
    If d.Exists("") Then d.Remove "" 'remove blank
    obj.List = d.keys

End Sub

PLEASE NOTE : In this i get permission denied ! run time error 70

 

[Akuini]

PLEASE NOTE : In this i get permission denied ! run time error 70

Make sure the Rowsource of the Combobox is empty; you can check that in the Property Window

 

[Khanzaki]

Wow ! That worked ! I had one row source for my first combo box ! Removed it and now your code works like a charm !
Thankyou so much @Akuini

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.

 

[Khanzaki]

@Akuini
Sir , I may be getting too enthusiastic i reckon but is there any way to get list of values from multiple worksheets ?
I mean the current code works very fine !
But I have to add this code for every individual worksheet where a unique list is needed ( as my comboboxes are a too many and do not have one single worksheet as source !

I have was thinking of something that makes this line a variable to populate comboboxes named cb1 cb2 from worksheet named for eg. sheet 2 column D ; sheet3 column J respectively

With Sheets("OPD")  'adjust sheet reference

I hope I am making some sense here

 

[Akuini]

@Akuini

I have was thinking of something that makes this line a variable to populate comboboxes named cb1 cb2 from worksheet named for eg. sheet 2 column D ; sheet3 column J respectively

With Sheets("OPD")  'adjust sheet reference

Try this one:

Private Sub UserForm_Initialize()
'adjust combobox, sheet name & column reference
to_List ComboBox1, "Sheet1", "D"
to_List ComboBox2, "Sheet2", "E"
to_List ComboBox3, "Sheet3", "H"
to_List ComboBox4, "Sheet4", "L"

End Sub


Sub to_List(obj As Object, Shn As String, col As String)
Dim d As Object, va, x
Set d = CreateObject("scripting.dictionary")

    With Sheets(Shn)
        va = .Range(.Cells(2, col), .Cells(.Rows.Count, col).End(xlUp)).Value  'data start at row 2
    End With

    For Each x In va
        d(x) = ""
    Next
    If d.Exists("") Then d.Remove "" 'remove blank
    obj.List = d.keys

End Sub