# combo box

#### davidson

##### New Member
Hi all,

I want to create a combo box and I have entered the list of values by fillinmg in the range in ListFillRange, say D1:D5. Howebver, among the values in D1 to D5, there are some duplicate. Can we have a combox box without any duplicate value?

For example, if the values of D1, D2, D3, D4, D5 are a, b, c, b, a

I would like to have a combo box showing only 3 values, namely a, b, c. But now I have the value a, b, c, b, a.

Can anyone help? Thx

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### agihcam

##### Well-known Member
welcome to the board.
try;
Code:
``````Dim a, e, dic As Object
With Sheets("sheet1") '<- change to suite
a = .Range("a1", .Range("a" & Rows.Count).End(xlUp)).Value '<- change to suite
End With
If Not IsArray(a) Then Exit Sub
Set dic = CreateObject("Scripting.Dictionary")
dic.comparemode = vbTextCompare
For Each e In a
If Not IsEmpty(e) Then
If Not dic.exists(e) Then
End If
End If
Next
Erase a
If dic.Count > 1 Then Me.ComboBox1.List = dic.keys
Set dic = Nothing``````

#### davidson

##### New Member
welcome to the board.
try;
Code:
``````Dim a, e, dic As Object
With Sheets("sheet1") '<- change to suite
a = .Range("a1", .Range("a" & Rows.Count).End(xlUp)).Value '<- change to suite
End With
If Not IsArray(a) Then Exit Sub
Set dic = CreateObject("Scripting.Dictionary")
dic.comparemode = vbTextCompare
For Each e In a
If Not IsEmpty(e) Then
If Not dic.exists(e) Then
End If
End If
Next
Erase a
If dic.Count > 1 Then Me.ComboBox1.List = dic.keys
Set dic = Nothing``````

Can you give me an example on how to change this line?

a = .Range("a1", .Range("a" & Rows.Count).End(xlUp)).Value '<- change to suite

Thanks a lot.

#### davidson

##### New Member
welcome to the board.
try;
Code:
``````Dim a, e, dic As Object
With Sheets("sheet1") '<- change to suite
a = .Range("a1", .Range("a" & Rows.Count).End(xlUp)).Value '<- change to suite
End With
If Not IsArray(a) Then Exit Sub
Set dic = CreateObject("Scripting.Dictionary")
dic.comparemode = vbTextCompare
For Each e In a
If Not IsEmpty(e) Then
If Not dic.exists(e) Then
End If
End If
Next
Erase a
If dic.Count > 1 Then Me.ComboBox1.List = dic.keys
Set dic = Nothing``````

Can you give me an example on how to change this line?

a = .Range("a1", .Range("a" & Rows.Count).End(xlUp)).Value '<- change to suite

Thanks a lot.

#### agihcam

##### Well-known Member

Can you give me an example on how to change this line?

a = .Range("a1", .Range("a" & Rows.Count).End(xlUp)).Value '<- change to suite

this line will capture the data from A1:A& last row

if your data was in columnC for example then simply change "a1" to "c1" and .range("a" & rows.count...... to .range("c" & rows.count.....

EDIT: This code is from Jindon, so the credit goes to him. forgot to mentioned this in the first place (sorry).

#### davidson

##### New Member
Thanks. I have tried this by adding your code in the Private Sub ComboBox1_Change().

However, there is an error on this part Me.ComboBox1.List = dic.keys
when I change the comboBox.

May I know how to change it? Thanks

davidson,

#### davidson

##### New Member

I have created a combBox1 and then add the following code.

Private dic As Object

Private Sub ComboBox1_Change()
Dim A, e, dic As Object
With Sheets("custom size")
A = .Range("a1", .Range("a" & Rows.Count).End(xlUp)).Value
End With
If Not IsArray(A) Then Exit Sub
Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMOde = vbTextCompare
For Each e In A
If Not IsEmpty(e) Then
If Not dic.exists(e) Then
End If
End If
Next
Erase A
If dic.Count > 1 Then Me.ComboBox1.List = dic.keys <-ERROR in this line
Set dic = Nothing

End Sub

Thanks a lot.

#### jindon

##### MrExcel MVP
Looks like you only have one combobox...
delete "Private dic As Object"
and try
Code:
``````Private Sub ComboBox1_Change()
Dim A, e, dic As Object
With Sheets("custom size")
A = .Range("a1", .Range("a" & Rows.Count).End(xlUp)).Value
End With
If Not IsArray(A) Then Exit Sub
Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = vbTextCompare
For Each e In A
If Not IsEmpty(e) Then
If Not dic.exists(e) Then
End If
End If
Next
Erase A
If dic.Count > 0 Then Me.ComboBox1.List = dic.keys
Set dic = Nothing
End Sub``````

#### davidson

##### New Member
I still have the error in the line Me.ComboBox1.List = dic.keys
The error is 70 No authorization.

Replies
9
Views
149
Replies
7
Views
143
Replies
4
Views
687
Replies
5
Views
440
Replies
4
Views
600

1,141,913
Messages
5,709,298
Members
421,625
Latest member
renrut_5

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back