How to use a ComboBox with autocomplete and search as you type, but ignore blank formula or empty cells.

[Slayer_17]

Hi every one, sorry for my bad english
i found this thread on my searches and i loved the answer of the post#2

How to use a ComboBox with autocomplete and search as you type.

Hi, I have a ComboBox embedded in a worksheet that references a list of items. Ie: small cat cat big cat small dog dog big dog Right now when I type in 'cat' i only get 'cat' as the suggestion. What I would like is to get all items that contain 'cat'., ie., in this case: small cat cat big...

www.mrexcel.com

Option Explicit

Dim IsArrow As Boolean
Dim ListRowsMaximum As Long
Dim ListRange As Range


Private Sub Init_Settings()

    'ListRange holds the cells to use in the combobox List
    
    With Worksheets("Data2")
        Set ListRange = .Range("A2", .Cells(Rows.Count, "A").End(xlUp)) ' <-----I think it must be here that I should change or add another variable but I don't know how
    End With

    'ListRowsMaximum is the original ListRows value - maximum number of displayed rows
    
    If ListRowsMaximum = 0 Then ListRowsMaximum = Me.ComboBox1.ListRows
    
End Sub


Private Sub ComboBox1_GotFocus()

    If ListRange Is Nothing Then Init_Settings
    
    'Initialise the combobox List with cell values from ListRange
    
    With Me.ComboBox1
        .List = ListRange.Value
        .ListRows = Application.WorksheetFunction.Min(ListRowsMaximum, .ListCount)
        .Text = ""
    End With
    
End Sub


Private Sub ComboBox1_DropButtonClick()
    
    If ListRange Is Nothing Then Init_Settings
    
    With Me.ComboBox1
        .List = ListRange.Value
        .ListRows = Application.WorksheetFunction.Min(ListRowsMaximum, .ListCount)
        .DropDown
    End With
    
End Sub


Private Sub ComboBox1_Change()

    Dim i As Long
    
    'Update the combobox List to only the items containing the current Text
    
    If Not IsArrow Then
        With Me.ComboBox1
            .List = ListRange.Value
            If Len(.Text) Then
                For i = .ListCount - 1 To 0 Step -1
                    If InStr(1, .List(i), .Text, vbTextCompare) = 0 Then .RemoveItem i

                Next
            End If
            .DropDown
            .ListRows = Application.WorksheetFunction.Min(ListRowsMaximum, .ListCount)
        End With
    End If
    
End Sub


Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        
    With Me.ComboBox1
        .ListRows = Application.WorksheetFunction.Min(ListRowsMaximum, .ListCount)
    End With
        
    IsArrow = (KeyCode = vbKeyUp) Or (KeyCode = vbKeyDown)
    
    If KeyCode = vbKeyReturn Then
        Me.ComboBox1.List = ListRange.Value
    ElseIf KeyCode = vbKeyTab Then
        'Tab key selects first displayed item or highlighted item
        With Me.ComboBox1
            If .ListIndex = -1 Then
                .Value = .List(0)
            Else
                .Value = .List(.ListIndex)
            End If
        End With
        KeyCode = vbKeyReturn
    End If
    
End Sub

I tried everything and researched a lot how to make this code skip lines that have blank formula results and cells that don't contain data, but I failed miserably.
I understand little about excel vba, and I can only often join the codes in my spreadsheets.

I really appreciate the help in advance.

 

[sijpie]

can you explain better what you need. I thought the code we had would do autocomplete for any listbox.
What do you mean with this one also needs unique values? What is different in this listbox compared to the others?
I don't know where you got that snippet of code from. is it VBA or VB? I don't know of any VBA abject that has the method .Contains. A VBA dictionary has the .Exists method.

Anyway let me know what you are trying to achieve, that makes it easier to code it.