a slightly different version
Private Sub UserFilter_Change()
Dim a, i&, k&
Filternames.Clear
With Sheets("Customers")
a = .Range("B2:F" & .Cells(Rows.Count, 2).End(xlUp).Row).Value
End With
If optCompany = True Then
k = 1
ElseIf optAddress = True Then
k = 2
ElseIf optPostcode = True Then
k = 5
End If
With CreateObject("Scripting.Dictionary")
.CompareMode = vbTextCompare
For i = 1 To UBound(a)
If InStr(a(i, k), UserFilter.Value) Then .Item(a(i, k)) = Array(a(i, 1), a(i, 2), a(i, 5))
Next
If .Count = 0 Then Exit Sub
If .Count > 1 Then
Filternames.List = Application.Index(.items, 0, 0)
Else
Filternames.Column = Application.Index(.items, 0, 0)
End If
End With
End Sub
Bookmarks