Hi all,

Firstly, how can I populate a combobox on a userform from range o
sheet1 using vba, lets say "A2:A4".

Secondly, if this range contains duplicate values how can i "groupby
to produce a non dupliacted list of values for the combobox.

Regards,

And

--
ex130
-----------------------------------------------------------------------
ex1302's Profile: http://www.excelforum.com/member.php...fo&userid=2521
View this thread: http://www.excelforum.com/showthread.php?threadid=38930

Hi,

You can normally populate a combobox from range using the following
instruction:

ComboBox1.List=Range("A2:A10")

However, if you want to remove dups, you could try this code:

Private Sub UserForm_Initialize()
Dim c As Range, D As Object
Set D = CreateObject("Scripting.Dictionary")
For Each c In Sheets("Sheet1").Range("A2:A10")
If Not D.Exists(c.Value) Then D.Add c.Value, 1
Next c
ComboBox1.List = Application.Transpose(D.Keys)
End Sub

Regards,
KL


"ex1302" wrote in
message ...

Hi all,

Firstly, how can I populate a combobox on a userform from range on
sheet1 using vba, lets say "A2:A4".

Secondly, if this range contains duplicate values how can i "groupby"
to produce a non dupliacted list of values for the combobox.

Regards,

Andy


--
ex1302
------------------------------------------------------------------------
ex1302's Profile:
http://www.excelforum.com/member.php...o&userid=25217
View this thread: http://www.excelforum.com/showthread...hreadid=389308

Thanks, it works great.....

can this be extended to work across two columns?

ie A1:B2

Regards,

And

--
ex130
-----------------------------------------------------------------------
ex1302's Profile: http://www.excelforum.com/member.php...fo&userid=2521
View this thread: http://www.excelforum.com/showthread.php?threadid=38930

try

Code
-------------------

Private Sub UserForm_Initialize()
Dim dic As Object, x, r As Range
Set dic = CreateObject("Scripting.Dictionary")
With Sheets("sheet1")
For Each r In .Range("a2:a4")
If Not IsEmpty(r) And Not dic.exists(r.Value) Then
dic.Add r.Value, Nothing
End If
Next
End With
x = dic.keys
Me.ComboBox1.List() = x
Set dic = Nothing: Erase x
End Sub

-------------------

--
jindo
-----------------------------------------------------------------------
jindon's Profile: http://www.excelforum.com/member.php...fo&userid=1313
View this thread: http://www.excelforum.com/showthread.php?threadid=38930