Delete the entire row if it's column does NOT have specific value

[An Quala]

Hello Everyone, few days back @Peter_SSs gave me a code which deletes the entire rows if Column P contains a word in phrase match from in Range Y, it is working fine but I also want a inverse code of this, which works exactly opposite as instead of deleting the rows which fulfill the criteria, it should delete the rows which do NOT fulfill the criteria, for example if 'Control Panel'!Y59-YDown have words Apple, Banana, Orange so any row in 'Sponsored Products Campaigns'! Column P that does NOT contain these words will be deleted, it is exactly opposite of the last code actually,

Here are the details:

1st: Pick the values from the range of Y59-YDown in "Control Panel" and delete the entire rows if that value does NOT come in phrase form of the column P of sheet "Sponsored Products Campaigns", now ideally we should be able to leave any cell blank in the range, and it will just ignore this cell but if not leaving any cell blank code - makes the things easier or faster, we can go with that way as well,

Now the 2nd and 3rd part of this code will be:

Instead of range Y59-YDown, we have AA59-AADown and AC59-ACdown from the same sheet "Control Panel" but for AA, we will check the value in Column O in sheet "Sponsored Brands Campaigns" and for AC, we will also check in the Column O but in sheet "Sponsored Display Campaigns",

But important point is that these all 3 codes should be separated from each other because they will be running in different parts of a single SUB, so they should be independent.

I am attaching the previous code which just needs to be adjusted to work in a opposite way, BUT before instead of Y,AA and AC, it was S,U and W for lookup array.

Sub Delete_Rows(CP_KeyWordCol As String, ShName As String, ColToCheck As String)
  Dim RX As Object
  Dim a As Variant, b As Variant
  Dim nc As Long, i As Long, k As Long
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.IgnoreCase = True
  With Sheets("Control Panel")
  If .Range(CP_KeyWordCol & Rows.Count).End(xlUp).Row >= 59 Then
    a = Application.Transpose(.Range(CP_KeyWordCol & "59", .Range(CP_KeyWordCol & Rows.Count).End(xlUp)).Value)
    If VarType(a) = vbVariant + vbArray Then
      RX.Pattern = "\b(" & Replace(Join(Filter(Split("#" & Join(a, "#|#"), "|"), "##", False), "|"), "#", "") & ")\b"
    Else
      RX.Pattern = "\b" & a & "\b"
    End If
    End If
  End With
  
If Len(RX.Pattern) > 0 Then
  With Sheets(ShName)
    nc = .Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
    a = .Range(ColToCheck & "2", .Range(ColToCheck & Rows.Count).End(xlUp)).Value
    ReDim b(1 To UBound(a), 1 To 1)
    For i = 1 To UBound(a)
      If RX.Test(a(i, 1)) Then
        b(i, 1) = 1
        k = k + 1
      End If
    Next i
    If k > 0 Then
      Application.ScreenUpdating = False
      With .Range("A2").Resize(UBound(a), nc)
        .Columns(nc).Value = b
        .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo
        .Resize(k).EntireRow.Delete
      End With
      Application.ScreenUpdating = True
    End If
  End With
  End If
End Sub

Delete_Rows "S", "Sponsored Products Campaigns", "P"

Delete_Rows "U", "Sponsored Brands Campaigns", "O"

Delete_Rows "W", "Sponsored Display Campaigns", "O"

Thank you.

 

[An Quala]

Hello @Peter_SSs Could you please do that?