VBA Code for deleting complete rows if it has specific text

[An Quala]

Hi, so I need to delete the complete row if specific criteria is met, the criteria is as follows,
In a sheet named called "Data"

If column B contains either of "John", "David", "Susan", "Macro", few of them, or all of them, then delete those specific rows leaving the others (Note: If any of these names are not available in the first place then code should not stop.

Then if Column K contains either of "USA", "UK", "DE", "AU", "CA", few of them, or all of them then again delete the full rows

Really appreciate the help!

Thank you.

 

[An Quala]

@kevin9999 Hi, I will need this code for 3 sheets named "Sponsored Products", "Sponsored Brands" and "Sponsored Display" but for every sheet the criteria will be different which i think i will be able to edit.

 

[kevin9999]

@kevin9999 Hi, I will need this code for 3 sheets named "Sponsored Products", "Sponsored Brands" and "Sponsored Display" but for every sheet the criteria will be different which i think i will be able to edit.

Personally, if it was my project, I would split the different sheets into their own subs in the same module & run them sequentially. I would do it that way because you say you want to change the criteria for each sheet, and I feel using a Select Case option (or other) may get confusing. The following code assumes all 3 sheets have the same layout, and I've indicated where you change the criteria. Put all the code below in the same module, and run it by executing the Multiple_Sheets sub. All three subs will run automatically.

Option Explicit
Option Compare Text
Dim lr As Long, lc As Long, i As Long
Dim ws As Worksheet
Dim a, b
Sub Multiple_Sheets()
    Application.ScreenUpdating = False
    Products
    Brands
    Display
    Application.ScreenUpdating = False
End Sub
Sub Products()
    Set ws = Worksheets("Sponsored Products")
    lr = ws.Cells.Find("*", , xlFormulas, , 1, 2).Row
    lc = ws.Cells.Find("*", , xlFormulas, , 2, 2).Column + 1
    
    a = Range(ws.Cells(2, 1), ws.Cells(lr, lc))
    ReDim b(1 To UBound(a), 1 To 1)
    
    '***CHANGE YOUR CRITERIA IN THIS BLOCK***
    For i = 1 To UBound(a)
        If Not a(i, 2) Like "*Keyword*" And _
        Not a(i, 2) Like "*Product Targeting*" And _
        Not a(i, 11) Like "*enabled*" Then b(i, 1) = 1
    Next i

    ws.Cells(2, lc).Resize(UBound(a)) = b
    i = WorksheetFunction.Sum(ws.Columns(lc))

    Range(ws.Cells(2, 1), ws.Cells(lr, lc)).Sort Key1:=ws.Cells(2, lc), order1:=1, Header:=2
    If i > 0 Then ws.Cells(2, lc).Resize(i).EntireRow.Delete
End Sub
Sub Brands()
    Set ws = Worksheets("Sponsored Brands")
    lr = ws.Cells.Find("*", , xlFormulas, , 1, 2).Row
    lc = ws.Cells.Find("*", , xlFormulas, , 2, 2).Column + 1
    
    a = Range(ws.Cells(2, 1), ws.Cells(lr, lc))
    ReDim b(1 To UBound(a), 1 To 1)
    
    '***CHANGE YOUR CRITERIA IN THIS BLOCK***
    For i = 1 To UBound(a)
        If Not a(i, 2) Like "*Keyword*" And _
        Not a(i, 2) Like "*Product Targeting*" And _
        Not a(i, 11) Like "*enabled*" Then b(i, 1) = 1
    Next i

    ws.Cells(2, lc).Resize(UBound(a)) = b
    i = WorksheetFunction.Sum(ws.Columns(lc))

    Range(ws.Cells(2, 1), ws.Cells(lr, lc)).Sort Key1:=ws.Cells(2, lc), order1:=1, Header:=2
    If i > 0 Then ws.Cells(2, lc).Resize(i).EntireRow.Delete
End Sub
Sub Display()
    Set ws = Worksheets("Sponsored Display")
    lr = ws.Cells.Find("*", , xlFormulas, , 1, 2).Row
    lc = ws.Cells.Find("*", , xlFormulas, , 2, 2).Column + 1
    
    a = Range(ws.Cells(2, 1), ws.Cells(lr, lc))
    ReDim b(1 To UBound(a), 1 To 1)

    '***CHANGE YOUR CRITERIA IN THIS BLOCK***
    For i = 1 To UBound(a)
        If Not a(i, 2) Like "*Keyword*" And _
        Not a(i, 2) Like "*Product Targeting*" And _
        Not a(i, 11) Like "*enabled*" Then b(i, 1) = 1
    Next i

    ws.Cells(2, lc).Resize(UBound(a)) = b
    i = WorksheetFunction.Sum(ws.Columns(lc))

    Range(ws.Cells(2, 1), ws.Cells(lr, lc)).Sort Key1:=ws.Cells(2, lc), order1:=1, Header:=2
    If i > 0 Then ws.Cells(2, lc).Resize(i).EntireRow.Delete
End Sub

 

[An Quala]

Hi @kevin9999 This time it is not working correctly, it could be due to AND operation, last time it was OR Operation, I think OR operation would be suitable.

Also I see what you mean by having all these separately, I will basically made this code, a part of a bigger code, and I will put all these 3 in a different positions that would be much cool (but I don't want to run these separately though),

For now let me elaborate my criteria a little more,

Delete entire row if any of the following condition is met:

In Sheets. 'Sponsored Products Campaigns'
>If Column B does NOT have "Keyword" or "Product Targeting" OR
>If Column S does NOT have "enabled" OR
>If Column AS does NOT have "enabled" OR
>If Column AT does NOT have "enabled"

In Sheets. 'Sponsored Brands Campaigns'
>If Column B does NOT have "Keyword" or "Product Targeting" OR
>If Column Q does NOT have "enabled" OR
>If Column R does NOT have "running" or "other"
>If Column AY does NOT have "enabled"

In Sheets. 'Sponsored Display Campaigns'
>If Column B does NOT have "Audience Targeting" or "Product Targeting" OR
>If Column M does NOT have "enabled" OR
>If Column AL does NOT have "enabled" OR
>If Column AM does NOT have "enabled"

 

[kevin9999]

Hi @kevin9999 This time it is not working correctly, it could be due to AND operation, last time it was OR Operation, I think OR operation would be suitable.

Also I see what you mean by having all these separately, I will basically made this code, a part of a bigger code, and I will put all these 3 in a different positions that would be much cool (but I don't want to run these separately though),

For now let me elaborate my criteria a little more,

Delete entire row if any of the following condition is met:

In Sheets. 'Sponsored Products Campaigns'
>If Column B does NOT have "Keyword" or "Product Targeting" OR
>If Column S does NOT have "enabled" OR
>If Column AS does NOT have "enabled" OR
>If Column AT does NOT have "enabled"

In Sheets. 'Sponsored Brands Campaigns'
>If Column B does NOT have "Keyword" or "Product Targeting" OR
>If Column Q does NOT have "enabled" OR
>If Column R does NOT have "running" or "other"
>If Column AY does NOT have "enabled"

In Sheets. 'Sponsored Display Campaigns'
>If Column B does NOT have "Audience Targeting" or "Product Targeting" OR
>If Column M does NOT have "enabled" OR
>If Column AL does NOT have "enabled" OR
>If Column AM does NOT have "enabled"

OK. Firstly, "This time it is not working correctly" tells me nothing useful. What is it not doing that it should be doing and/or what is it doing that it should not be doing?
Secondly, you seem to have changed the sheet names since your last post. Thirdly, you have expanded the sizes of the sheets since you originally provided a sample in Post #4 via the XL2BB Tool - they now extend to column AY? I think it's time you created a new thread. Please outline your requirement as you have above, and provide samples of the data in all 3 sheets - and I will be happy to look at it again.

 

[Peter_SSs]

Continued here. This thread now closed so that we don't have two of them trying to do the same thing.