Sleeper: Customize KB Code to Delete Rows

[Anne Troy]

I want to delete rows according to ALL values in Column A of Sheet2 (there's about a dozen).

The column to look in is on sheet1, Column L.

So, if a specific word is found (yes, that's the only word in the cell) in Column L of Sheet1, the entire row it's found in is deleted.

Dave's got some code here; dunno if it can be altered.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=260

[Bob Phillips]

I want to delete rows according to ALL values in Column A of Sheet2 (there's about a dozen).

The column to look in is on sheet1, Column L.

So, if a specific word is found (yes, that's the only word in the cell) in Column L of Sheet1, the entire row it's found in is deleted.

Something like this?

Sub test()
    Dim iLastRow As Long
    Dim i As Long
    Dim colWords As Collection
Application.ScreenUpdating = False
Set colWords = New Collection
'build a collection of values to compare against
    'from the list in column A of Sheet2
    With Worksheets("Sheet2")
        For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
            If .Cells(i, "A").Value <> "" Then
                colWords.Add i, .Cells(i, "A").Value
            End If
        Next i
    End With
'loop through column L of Sheet1 and check if value is in
    'the collection, if so delete it
    With Worksheets("Sheet1")
        iLastRow = .Cells(Rows.Count, "L").End(xlUp).Row
        For i = iLastRow To 1 Step -1
            If ExistsInCollection(colWords, .Cells(i, "L").Value) Then
                .Rows(i).Delete
            End If
        Next i
    End With
Application.ScreenUpdating = True
End Sub
 
Public Function ExistsInCollection(col As Collection, ByVal sKey As String)
    On Error GoTo NoSuchKey
    If VarType(col.Item(sKey)) = vbObject Then
         ' force an error condition if key does not exist
    End If
    ExistsInCollection = True
    Exit Function
NoSuchKey:
    ExistsInCollection = False
End Function

[mdmackillop]

Hi DB,
Is this solved?