Filter listbox based on combobox - with multiple columns in the list box

[JohnSmith9945]

Hey everyone,

I found some code in an old post about how to filter the list box based on a combo selection - however i cant get it to work with multiple columns, how would i ammend the code to make it work?

context: Im trying to use the combo box as a filter for the listbox to show all records that have that ID.

Dim rngData As Range

Private Sub UserForm_Initialize()
'This Event runs when Userform1 is initialized
'Change here to suit your data
    Set rngData = Sheets("Sheet1").Range("A2:B9")

    'Set unique Option list to ComboBox1
    Me.ComboBox1.List = Array_Unique_Collection(rngData.Columns(1).Value)
End Sub

Private Sub ComboBox1_Change()
'This Event runs when ComboBox1 is changed
    Me.ListBox1.Clear

    For i = 1 To rngData.Rows.Count
        If rngData.Cells(i, 1).Value = ComboBox1.Value Then
            Me.ListBox1.AddItem rngData.Cells(i, 2).Value
        End If
    Next
End Sub

Function Array_Unique_Collection(ByVal NotUniqueArry As Variant) As Variant
'This is a function returns unique collection as a 1D array.
'returns NULL when there is no value
    Dim cTmp As New Collection
    Dim i As Long
    Dim aTmp As Variant
    Dim vElm As Variant

    On Error Resume Next
    For Each vElm In NotUniqueArry
        cTmp.Add CStr(vElm), CStr(vElm)
    Next
    On Error GoTo 0
    If cTmp.Count = 1 And cTmp.Item(1) = vbNullString Then
        Array_Unique_Collection = Null
        Exit Function
    End If
    ReDim aTmp(1 To cTmp.Count)
    For i = 1 To cTmp.Count
        aTmp(i) = cTmp.Item(i)
    Next
    Array_Unique_Collection = aTmp
End Function

Thanks!

 

[dmt32]

Hi,
your code is working with two columns (A&B) how many more do you want to include?
Helpful to forum if supply copy of or worksheet with dummy data using MrExcel Addin xl2BB

Dave