Listbox1 Contains text from listbox2-> delete item

Question

I have a VBA userform with two listboxes. I am struggling to make the following operation:

I need to check whether Listbox1 contains list items from Listbox2 and if it does, delete the items in Listbox1 that contain items from Listbox2. For example Item "purple monkey" in listbox1 contains "monkey" (item in listbox2), thus I need to delete the entire Item "purple monkey".

Could someone help me with that?

I use the following code to create the primary list (Keywords) and initialize the userform. Additionally I create a textbox, where user enters items and they are added to the Listbox2. This code works well:

Private Sub UserForm_Initialize()

    Application.Visible = False

    Keywords.SetFocus
    TextBox2.Value = NegKeyList.ListCount & "negative keys"

    Dim mycollection As Collection, cell As Range

    On Error Resume Next

    Set mycollection = New Collection

    With Keywords

        .Clear

        For Each cell In Worksheets("Rawdata").Range("A3:A" & Cells(Rows.Count, 1).End(xlUp).Row)
            If Len(cell) <> 0 Then
                Err.Clear

                mycollection.Add cell.Value, cell.Value
                If Err.Number = 0 Then .AddItem cell.Value

            End If
        Next cell

    End With

    MsgBox mycollection.Count & "added to the list"

    If Keywords.ListCount > 0 Then
        TextBox1.Value = Keywords.ListCount & " keys"
    End If

End Sub

I need now to make another function that a user can press a button and delete all the Keywords that contain the ListBox2 items (not necessarily equal to them).

Answer 1

Assuming you have a command button (CommandButton1) on your userform, you can put the code in the CommandButton1_Click() event. Since some items may be deleted from ListBox1, the counter1 needs to step from the max to min to avoid any Array index issues from the counter becoming greater than the max index.

Dim counter1 as Long
Dim counter2 as Long

For counter1 = ListBox1.ListCount - 1 to 0 Step -1 'Indexes are 0-based, so max index is count - 1
    For counter2 = 0 to ListBox2.ListCount - 1
        If InStr(1, ListBox1.List(counter1), ListBox2.List(counter2)) > 0 Then 'InStr returns 0 when there's no match
            ListBox1.RemoveItem counter1
            Exit For 'Skip any more compares for the deleted Item
        End If
    Next counter2
Next counter1