Delete row for matched string

[redspanna]

Hey all

So I have this code that looks down sheet 1 ,column A and if a match for the stated string is found then the coresponding row is deleted.

How can the code be changed so instead of me adding each 'string' to form part of the code , the code would check down a list held in column A of Sheet3. Each time a match is found to that list then the row is deleted back in sheet 1

Sub DeleteRowWithContents()

    Sheets("Sheet1").Select
    Last = Cells(Rows.Count, "A").End(xlUp).Row
    For I = Last To 1 Step -1
        If (Cells(I, "A").Value) = "SALARY" Then
        Cells(I, "A").EntireRow.Delete
        Else
        If (Cells(I, "A").Value) = "WORKING TAX CREDIT" Then
        Cells(I, "A").EntireRow.Delete
        End If
        End If
        Next I
    End Sub

thanks in advance

 

[Anthony47]

Replace you For I /Next I loop with

    For I = Last To 1 Step -1
        If Application.WorksheetFunction.CountIf(Sheets("Sheet3").Range("A1:A100"), Cells(I, "A").Value) > 0 Then
            Cells(I, "A").EntireRow.Delete
        End If
    Next I

Then create the list in Sheet3!A1:A100, INCLUDING the item "SALARY" and run your macro

Bye

 

[Micron]

Methinks you'll need nested loops; one for looping over the range of values from column A and one for looping over your values to be found (which I guess you already have working). I've been told here before not to refer to a sheet when looping as it's very inefficient, thus the suggestion to use an array (it's loaded once into memory).

This could build your array of values to look for, and rather than debug.print as I did, your inner loop would delete the row where the array value is found. When the outer loop exits, the inner loop would advance by one (i.e. pick the next word to look for). Replace "WORKING TAX CREDIT" with ary(i,1).

Dim i As Integer, lastRow As Integer
Dim ary() As Variant

lastRow = Sheets("Sheet3").Cells(Rows.count, "F").End(xlUp).Row
ary = Range("F1:F" & lastRow)
For i = 1 To UBound(ary)
   Debug.Print ary(i, 1)
Next

 

[redspanna]

Replace you For I /Next I loop with

    For I = Last To 1 Step -1
        If Application.WorksheetFunction.CountIf(Sheets("Sheet3").Range("A1:A100"), Cells(I, "A").Value) > 0 Then
            Cells(I, "A").EntireRow.Delete
        End If
    Next I

Then create the list in Sheet3!A1:A100, INCLUDING the item "SALARY" and run your macro

Bye

Perfect - thanks a lot

 

[redspanna]

Methinks you'll need nested loops; one for looping over the range of values from column A and one for looping over your values to be found (which I guess you already have working). I've been told here before not to refer to a sheet when looping as it's very inefficient, thus the suggestion to use an array (it's loaded once into memory).

This could build your array of values to look for, and rather than debug.print as I did, your inner loop would delete the row where the array value is found. When the outer loop exits, the inner loop would advance by one (i.e. pick the next word to look for). Replace "WORKING TAX CREDIT" with ary(i,1).

Dim i As Integer, lastRow As Integer
Dim ary() As Variant

lastRow = Sheets("Sheet3").Cells(Rows.count, "F").End(xlUp).Row
ary = Range("F1:F" & lastRow)
For i = 1 To UBound(ary)
   Debug.Print ary(i, 1)
Next

Thanks for your reply

 

[Anthony47]

Thank you for your feedback