How to remove duplicates from dropdown list combobox
Category: [Excel] Demo Available
How to remove duplicates from dropdown list combobox
Requirement:
The user needs the additional code for preventing the duplicates from the dropdown list; because this code lists the whole data without removing the duplicates. The user has attached the example file below:
Private Sub ComboBox1_Change()
Dim i As Long
With Me.ComboBox1
If Not IsArrow Then .List = Range("AccountsList").Value
If .ListIndex = -1 And Len(.Text) Then
For i = .ListCount - 1 To 0 Step -1
If InStr(1, .List(i), .Text, 1) = 0 Then .RemoveItem i
Next i
.DropDown
End If
End With
End Sub
Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
IsArrow = KeyCode = vbKeyUp Or KeyCode = vbKeyDown
If KeyCode = vbKeyReturn Then Me.ComboBox1.List = Range("AccountsList").Value
End Sub
Solution:
Code:
Private IsArrow As Boolean
Option Explicit
Dim MyArray
Private Sub ComboBox1_Change()
Dim i As Long
With Me.ComboBox1
If Not IsArrow Then .List = MyArray 'Range("AccountsList").Value
If .ListIndex = -1 And Len(.Text) Then
For i = .ListCount - 1 To 0 Step -1
If InStr(1, .List(i), .Text, 1) = 0 Then .RemoveItem i
Next i
.DropDown
End If
End With
End Sub
Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
IsArrow = KeyCode = vbKeyUp Or KeyCode = vbKeyDown
If KeyCode = vbKeyReturn Then Me.ComboBox1.List = MyArray 'Range("AccountsList").Value
End Sub
Private Sub UserForm_Initialize()
Dim db
With CreateObject("scripting.dictionary")
For Each db In Range("AccountsList").Value
If Not .exists(db) Then
.Add db, db
End If
Next
MyArray = .keys
End With
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by pike.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use a dropdown or text option |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.