VBA macro to delete row if column contains same text from another sheet

[jo206]

Hi all,

I am looking for a macro that can delete rows in Sheet 2 Column A if words "apple juice" or "cookie" are found, but those two words are on Sheet 3 Column A and locked

It has to be if Sheet 2 Col A contains those words, not exact match

so my end goal is Sheet 3 acts as a dictionary and I'll keep adding words there, and everytime I import a new data in sheet 2 I can just run a macro and remove rows if those words are found in sheet 3.

thank you so much in advance!

 

[Legacy 143009]

I hope it works:

Sub test()
  Dim dic As Variant, records As Variant
  With Worksheets("Sheet3")
    dic = Intersect(.UsedRange, .Columns("A"))
  End With
  With Worksheets("Sheet2")
    records = Intersect(.UsedRange, .Columns("A"))
    For i = UBound(records) To 1 Step -1
      For Each itm In dic
        If records(i, 1) Like "*" & itm & "*" Then
          .Rows(i).EntireRow.Delete
        End If
      Next
    Next
  End With
End Sub

 

[jo206]

I hope it works:

Sub test()
  Dim dic As Variant, records As Variant
  With Worksheets("Sheet3")
    dic = Intersect(.UsedRange, .Columns("A"))
  End With
  With Worksheets("Sheet2")
    records = Intersect(.UsedRange, .Columns("A"))
    For i = UBound(records) To 1 Step -1
      For Each itm In dic
        If records(i, 1) Like "*" & itm & "*" Then
          .Rows(i).EntireRow.Delete
        End If
      Next
    Next
  End With
End Sub

thanks a lot! it works perfectly.

 

[Legacy 143009]

I am glad it did help! Thanks for the feedback