Delete rows from excel using values from a column in another sheet?

Question

I am working on an excel worksheet with multiple columns and roughly 6000 rows. Sheet1 will contain the main information.(the 6000 rows and columns to R). Sheet2 is my exceptions list I need to use for filtering/deleting these rows.

Right now this is what I am using and it only finds exact matches. I need this to find exceptions from sheet2 even if they are part of another word.

For example: when I run this it will find and delete every row that contains just the word hello. but not hello world or hello foo. I need this to delete the row with hello world and hello foo as well.

I am trying to set this up this way so i can simply add more items to my exceptions list and delete more rows as needed.

Sub CheckA()
Dim LR As Long, i As Long
With Sheets("IR Temp")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = LR To 1 Step -1
        If IsNumeric(Application.Match(.Range("A" & i).Value, Sheets("Exceptions").Columns("A"), 0)) Then .Rows(i).Delete
    Next i
End With
End Sub

How can I make this less specific? I understand it is working how it is supposed to and is finding the exact match but I need it to find and delete the row if that value is found with any combinations of other characters with it.

Answer 1

The MATCH function can accept wildcard matches but you need to reverse the way you are identifying the rows to delete. Simply prefix and suffix the criteria rtem with asterisks (e.g Chr(42) ).

Your code did not remove more than a single occurrence of the criteria value. Probably better to loop it until there are no more matches. Since MATCH returns an error on no-match, it might be better to rely on the COUNTIF function to return a value greter than zero.

Sub CheckA()
    Dim str As String, a As Long, vSTRs As Variant

    With Worksheets("Exceptions")
        vSTRs = .Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp)).Value2
    End With

    With Sheets("IR Temp")
        For a = LBound(vSTRs, 1) To UBound(vSTRs, 1)
            If CBool(Len(Trim(CStr(vSTRs(a, 1))))) Then
                str = Chr(42) & vSTRs(a, 1) & Chr(42)
                Do While CBool(Application.CountIf(.Columns(1), str))
                    .Rows(Application.Match(str, .Columns(1), 0)).EntireRow.Delete
                Loop
            End If
        Next a
    End With
End Sub

This will be a little more time-consuming than identifing a block or union of non-contiguous rows to delete but it will get the job done. Turn off environment variables like screen updating and calculation to speed things up once it runs to your satisfaction.

Answer 2

The below assumes your list of words to search for is in Sheet2 column A and your list to check them in are Sheet1 column A starting row 2. There may be a nicer way than a nested loop, but what we have here is putting your list of words into an array, looping through all the cells we want to see if they contain one of the words and for each of those cells looping through to check if one of the listed words is there.

Public Sub testing()

Dim x As Integer
Dim i As Integer
Dim ws As Worksheet
Dim listws As Worksheet
Dim endList As Integer
Dim endR As Integer
Dim arr() As Variant

Set ws = ThisWorkbook.Worksheets("Sheet1")
endR = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Set listws = ThisWorkbook.Worksheets("Sheet2")
endList = listws.Cells(ws.Rows.Count, "A").End(xlUp).Row
arr = listws.Range("A1:A" & endList)

x = 2
While x <= endR
    For i = 1 To UBound(arr, 1)
        If InStr(ThisWorkbook.Worksheets("Sheet1").Cells(x, 1).Value, arr(i, 1)) > 0 Then
            ThisWorkbook.Worksheets("Sheet1").Cells(x, 1).EntireRow.Delete
        End If
    Next i
    x = x + 1
Wend

End Sub