VBA Code for deleting complete rows if it has specific text

Status
Not open for further replies.
An Quala

An Quala

Board Regular
Joined
Mar 21, 2022
Messages
146
Office Version
  1. 2021
Platform
  1. Windows
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.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
Since you did not provide any sample data to test, here is some air code (untested) for you to try out.

VBA Code: 
Option Explicit

Sub AnQuala()
Dim lr As Long, i As Long
lr = Range("B" & Rows.Count).End(xlUp).Row
For i = lr To 1 Step -1
If Range("B" & i) = "John" Or Range("B" & i) = "David" Or Range("B" & i) = "Susan" Or Range("B" & i) = "Macro" Then
Range("B" & i).EntireRow.Delete
End If
Next i
lr = Range("K" & Rows.Count).End(xlUp).Row
For i = lr To 1 Step -1
If Range("K" & i) = "USA" Or Range("K" & i) = "UK" Or Range("K" & i) = "DE" Or Range("K" & i) = "AU" Or Range("K" & i) = "CA" Then
Range("K" & i).EntireRow.Delete
End If
MsgBox "completed"
End Sub
 
Upvote 0
alansidman said:
Since you did not provide any sample data to test, here is some air code (untested) for you to try out.

VBA Code: 
Option Explicit

Sub AnQuala()
Dim lr As Long, i As Long
lr = Range("B" & Rows.Count).End(xlUp).Row
For i = lr To 1 Step -1
If Range("B" & i) = "John" Or Range("B" & i) = "David" Or Range("B" & i) = "Susan" Or Range("B" & i) = "Macro" Then
Range("B" & i).EntireRow.Delete
End If
Next i
lr = Range("K" & Rows.Count).End(xlUp).Row
For i = lr To 1 Step -1
If Range("K" & i) = "USA" Or Range("K" & i) = "UK" Or Range("K" & i) = "DE" Or Range("K" & i) = "AU" Or Range("K" & i) = "CA" Then
Range("K" & i).EntireRow.Delete
End If
MsgBox "completed"
End Sub
Click to expand...
It is giving for without next error
 
Upvote 0
Here is the code for you to test


bulk-a2upeur82nvj8s-20220318-20220324-1648237318772.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQ
1ProductEntityOperationCampaign IdAd Group IdPortfolio IdAd Id (Read only)Keyword Id (Read only)Product Targeting Id (Read only)Campaign NameAd Group NameStart DateEnd DateTargeting TypeStateDaily BudgetSKUASINAd Group Default BidBidKeyword TextMatch TypeBidding StrategyPlacementPercentageProduct Targeting ExpressionImpressionsClicksClick-through RateSpendSalesOrdersUnitsConversion RateAcosCPCROASCampaign Name (Informational only)Ad Group Name (Informational only)Campaign State (Informational only)Ad Group State (Informational only)Ad Group Default Bid (Informational only)Resolved Product Targeting Expression (Informational only)
2Sponsored ProductsJohn51810182230962asd20220131MANUALenabled100.0Dynamic bids - down only2739100.37%5.00.0000.00.0%0.50.0enabledenabled
3Sponsored ProductsDavid51810182230962Dynamic bids - down onlyplacementProductPage0.0248420.08%0.90.0000.00.0%0.450.0enabledenabled
4Sponsored ProductsDavid51810182230962Dynamic bids - down onlyplacementTop0.011686.9%4.10.0000.00.0%0.510.0enabledenabled
5Sponsored ProductsAd Group51810182230962103843358501771UKenabled0.42739100.37%5.00.0000.00.0%0.50.0enabledenabledenabledenabled
6Sponsored ProductsProduct Ad51810182230962103843358501771187095918266508enabledasd2739100.37%5.00.0000.00.0%0.50.0enabledenabledenabledenabled
7Sponsored ProductsKeyword51810182230962103843358501771133091597391670enabled0.58asdbroad000.0%0.00.0000.00.0%0.00.0enabledenabledenabledenabled0.4
8Sponsored ProductsKeyword5181018223096210384335850177120484689160127enabled0.64asdbroad2085100.48%5.00.0000.00.0%0.50.0enabledenabledenabledenabled0.4
9Sponsored ProductsKeyword51810182230962103843358501771147161619594891enabled0.58asdbroad2300.0%0.00.0000.00.0%0.00.0enabledenabledenabledenabled0.4
10Sponsored ProductsKeyword51810182230962103843358501771117074780596747enabled0.62asdbroad39400.0%0.00.0000.00.0%0.00.0enabledenabledenabledenabled0.4
11Sponsored ProductsKeyword51810182230962103843358501771198054310782002enabled0.64asdbroad000.0%0.00.0000.00.0%0.00.0enabledenabledenabledenabled0.4
12Sponsored ProductsKeyword51810182230962103843358501771216796694767871enabled0.64asdbroad23100.0%0.00.0000.00.0%0.00.0enabledenabledenabledenabled0.4
13Sponsored ProductsKeyword51810182230962103843358501771116800394856451enabled0.64asdbroad600.0%0.00.0000.00.0%0.00.0enabledenabledenabledenabled0.4
14Sponsored ProductsJohn224654867824700asd20220101MANUALenabled100.0asdDynamic bids - down only4800.0%0.00.0000.00.0%0.00.0enabledenabledenabled
15Sponsored ProductsDavid224654867824700asdDynamic bids - down onlyplacementProductPage0.03900.0%0.00.0000.00.0%0.00.0enabledenabledenabled
16Sponsored ProductsDavid224654867824700asdDynamic bids - down onlyplacementTop0.0000.0%0.00.0000.00.0%0.00.0enabledenabledenabled
17Sponsored ProductsAd Group224654867824700186267109368336USAenabled1.1asd4800.0%0.00.0000.00.0%0.00.0enabledenabledenabledenabled
18Sponsored ProductsProduct Ad22465486782470018626710936833652184089599015enabledasdasd4800.0%0.00.0000.00.0%0.00.0enabledenabledenabledenabled
19Sponsored ProductsKeyword22465486782470018626710936833683911309498353enabled1.08asdexact000.0%0.00.0000.00.0%0.00.0enabledenabledenabledenabled1.1
20Sponsored ProductsKeyword22465486782470018626710936833653354652434783enabled1.08asdexact000.0%0.00.0000.00.0%0.00.0enabledenabledenabledenabled1.1
21Sponsored ProductsKeyword2246548678247001862671093683362712747284010enabled0.97asdexact3400.0%0.00.0000.00.0%0.00.0enabledenabledenabledenabled1.1
22Sponsored ProductsKeyword2246548678247001862671093683368880509831632enabled0.97asdexact200.0%0.00.0000.00.0%0.00.0enabledenabledenabledenabled1.1
23Sponsored ProductsKeyword22465486782470018626710936833629871655919998enabled1.08asdexact100.0%0.00.0000.00.0%0.00.0enabledenabledenabledenabled1.1
24Sponsored ProductsKeyword224654867824700186267109368336207922361834615enabled1.57asdexact1000.0%0.00.0000.00.0%0.00.0enabledenabledenabledenabled1.1
25Sponsored ProductsKeyword22465486782470018626710936833682552476516641enabled0.8asdexact000.0%0.00.0000.00.0%0.00.0enabledenabledenabledenabled1.1
26Sponsored ProductsKeyword224654867824700186267109368336125041523559069enabled1.08asdexact000.0%0.00.0000.00.0%0.00.0enabledenabledenabledenabled1.1
27Sponsored ProductsKeyword224654867824700186267109368336178224892278959enabled1.17asdexact000.0%0.00.0000.00.0%0.00.0enabledenabledenabledenabled1.1
28Sponsored ProductsKeyword224654867824700186267109368336237702370532591enabled1.25asdexact100.0%0.00.0000.00.0%0.00.0enabledenabledenabledenabled1.1
29Sponsored ProductsJohn60228050322464asd20211221MANUALenabled100.0asdDynamic bids - down only100.0%0.00.0000.00.0%0.00.0enabledenabledenabled
30Sponsored ProductsDavid60228050322464asdDynamic bids - down onlyplacementProductPage0.0100.0%0.00.0000.00.0%0.00.0enabledenabledenabled
31Sponsored ProductsDavid60228050322464asdDynamic bids - down onlyplacementTop0.0000.0%0.00.0000.00.0%0.00.0enabledenabledenabled
32Sponsored ProductsAd Group60228050322464225369445700841DEenabled0.06asd100.0%0.00.0000.00.0%0.00.0enabledenabledenabledenabled
33Sponsored ProductsProduct Ad60228050322464225369445700841101252162650765DEenabledasdasd100.0%0.00.0000.00.0%0.00.0enabledenabledenabledenabled
34Sponsored ProductsKeyword60228050322464225369445700841232179385161330DEenabled0.11asdphrase100.0%0.00.0000.00.0%0.00.0enabledenabledenabledenabled0.06
35Sponsored ProductsKeyword60228050322464225369445700841255771627511436DEenabled0.12asdphrase000.0%0.00.0000.00.0%0.00.0enabledenabledenabledenabled0.06
36Sponsored ProductsKeyword60228050322464225369445700841176928454042161DEenabled0.12asdphrase000.0%0.00.0000.00.0%0.00.0enabledenabledenabledenabled0.06
37Sponsored ProductsKeyword6022805032246422536944570084181841757906585DEenabled0.12asdphrase000.0%0.00.0000.00.0%0.00.0enabledenabledenabledenabled0.06
38Sponsored ProductsKeyword6022805032246422536944570084180654821572179enabled0.12asdphrase000.0%0.00.0000.00.0%0.00.0enabledenabledenabledenabled0.06
39Sponsored ProductsKeyword60228050322464225369445700841273551451104575enabled0.12asdphrase000.0%0.00.0000.00.0%0.00.0enabledenabledenabledenabled0.06
40Sponsored ProductsKeyword6022805032246422536944570084140001743584860enabled0.12asdphrase000.0%0.00.0000.00.0%0.00.0enabledenabledenabledenabled0.06
41Sponsored ProductsKeyword60228050322464225369445700841259424722839019enabled0.12asdphrase000.0%0.00.0000.00.0%0.00.0enabledenabledenabledenabled0.06
42Sponsored ProductsKeyword60228050322464225369445700841278118504947134enabled0.12asdphrase000.0%0.00.0000.00.0%0.00.0enabledenabledenabledenabled0.06
43Sponsored ProductsKeyword6022805032246422536944570084198753070646170enabled0.12asdphrase000.0%0.00.0000.00.0%0.00.0enabledenabledenabledenabled0.06
44Sponsored ProductsJohn103977923004034asd20220201MANUALenabled100.0asdDynamic bids - down only57610.17%0.370.0000.00.0%0.370.0enabledenabledenabled
45Sponsored ProductsDavid103977923004034asdDynamic bids - down onlyplacementProductPage0.050110.2%0.370.0000.00.0%0.370.0enabledenabledenabled
46Sponsored ProductsDavid103977923004034asdDynamic bids - down onlyplacementTop0.0100.0%0.00.0000.00.0%0.00.0enabledenabledenabled
47Sponsored ProductsAd Group103977923004034221037315953408asdenabled0.4asd57610.17%0.370.0000.00.0%0.370.0enabledenabledenabledenabled
48Sponsored ProductsProduct Ad10397792300403422103731595340849309464963870enabledasdasd57610.17%0.370.0000.00.0%0.370.0enabledenabledenabledenabled
49Sponsored ProductsProduct Targeting103977923004034221037315953408186561934932770enabled0.48asdcategory="6292252011"57610.17%0.370.0000.00.0%0.370.0enabledenabledenabledenabled0.4category="Basic Dog Leashes"
50Sponsored ProductsJohn231897683081825asd20190918MANUALenabled500.0asdFixed bid11730460.39%35.2178.87330.0744.64%0.772.24enabledenabledenabled
51Sponsored ProductsDavid231897683081825asdFixed bidplacementProductPage0.010091110.11%8.0352.58220.1815.27%0.736.55enabledenabledenabled
52Sponsored ProductsDavid231897683081825asdFixed bidplacementTop0.0609264.27%19.2826.29110.0473.34%0.741.36enabledenabledenabled
53Sponsored ProductsAd Group23189768308182564716315089601asdenabled0.35asd11730460.39%35.2178.87330.0744.64%0.772.24enabledenabledenabledenabled
54Sponsored ProductsProduct Ad23189768308182564716315089601231724180855057enabledasdasd000.0%0.00.0000.00.0%0.00.0enabledenabledenabledenabled
55Sponsored ProductsProduct Ad23189768308182564716315089601148589570862064enabledasdasd164300.0%0.00.0000.00.0%0.00.0enabledenabledenabledenabled
56Sponsored ProductsProduct Ad23189768308182564716315089601198459992972544enabledasdasd5431290.53%21.3952.58220.0740.68%0.742.46enabledenabledenabledenabled
57Sponsored ProductsProduct Ad23189768308182564716315089601159962242985555enabledasdasd2006100.5%8.480.0000.00.0%0.850.0enabledenabledenabledenabled
58Sponsored ProductsProduct Ad2318976830818256471631508960186795561866493enabledasdasd265070.26%5.3426.29110.1420.31%0.764.92enabledenabledenabledenabled
59Sponsored ProductsKeyword23189768308182564716315089601107190882986enabled1.08asdexact000.0%0.00.0000.00.0%0.00.0enabledenabledenabledenabled0.35
60Sponsored ProductsKeyword23189768308182564716315089601250229049391726enabled1.08asdexact000.0%0.00.0000.00.0%0.00.0enabledenabledenabledenabled0.35
61Sponsored ProductsKeyword23189768308182564716315089601159629857812834enabled0.47asdexact000.0%0.00.0000.00.0%0.00.0enabledenabledenabledenabled0.35
62Sponsored ProductsKeyword2318976830818256471631508960199621606761191enabled0.97asdexact1900.0%0.00.0000.00.0%0.00.0enabledenabledenabledenabled0.35
63Sponsored ProductsKeyword23189768308182564716315089601262282056477120enabled1.08asdexact000.0%0.00.0000.00.0%0.00.0enabledenabledenabledenabled0.35
64Sponsored ProductsKeyword23189768308182564716315089601180153277825366enabled1.43asdexact2300.0%0.00.0000.00.0%0.00.0enabledenabledenabledenabled0.35
65Sponsored ProductsKeyword2318976830818256471631508960131116908056465enabled0.8asdexact000.0%0.00.0000.00.0%0.00.0enabledenabledenabledenabled0.35
Sheet1
 
Upvote 0
Missing a Next i..here
Rich (BB code): 
Sub AnQuala()
Dim lr As Long, i As Long
lr = Range("B" & Rows.Count).End(xlUp).Row
For i = lr To 1 Step -1
If Range("B" & i) = "John" Or Range("B" & i) = "David" Or Range("B" & i) = "Susan" Or Range("B" & i) = "Macro" Then
Range("B" & i).EntireRow.Delete
End If
Next i
lr = Range("K" & Rows.Count).End(xlUp).Row
For i = lr To 1 Step -1
If Range("K" & i) = "USA" Or Range("K" & i) = "UK" Or Range("K" & i) = "DE" Or Range("K" & i) = "AU" Or Range("K" & i) = "CA" Then
Range("K" & i).EntireRow.Delete
End If
Next i
MsgBox "completed"
End Sub
 
Upvote 0
Michael M said:
Missing a Next i..here
Rich (BB code): 
Sub AnQuala()
Dim lr As Long, i As Long
lr = Range("B" & Rows.Count).End(xlUp).Row
For i = lr To 1 Step -1
If Range("B" & i) = "John" Or Range("B" & i) = "David" Or Range("B" & i) = "Susan" Or Range("B" & i) = "Macro" Then
Range("B" & i).EntireRow.Delete
End If
Next i
lr = Range("K" & Rows.Count).End(xlUp).Row
For i = lr To 1 Step -1
If Range("K" & i) = "USA" Or Range("K" & i) = "UK" Or Range("K" & i) = "DE" Or Range("K" & i) = "AU" Or Range("K" & i) = "CA" Then
Range("K" & i).EntireRow.Delete
End If
Next i
MsgBox "completed"
End Sub
Click to expand...
Perfect, I just need to add sheet name as well as 'Sponsored Products' because I will make this code a part of the bigger code having multiple sheets, thanks!
 
Upvote 0
Another option.

VBA Code: 
Option Explicit
Sub AnQuala_Array()
    Dim lr As Long, lc As Long, i As Long
    Dim a, b
    lr = Cells.Find("*", , xlFormulas, , 1, 2).Row
    lc = Cells.Find("*", , xlFormulas, , 2, 2).Column + 1

    a = Range(Cells(2, 1), Cells(lr, lc))
    ReDim b(1 To UBound(a), 1 To 1)

    For i = 1 To UBound(a)
        If a(i, 2) Like "*John*" Or _
        a(i, 2) Like "*David*" Or _
        a(i, 2) Like "*Susan*" Or _
        a(i, 2) Like "*Macro*" Or _
        a(i, 11) Like "*USA*" Or _
        a(i, 11) Like "*UK*" Or _
        a(i, 11) Like "*DE*" Or _
        a(i, 11) Like "*AU*" Or _
        a(i, 11) Like "*CA*" Then b(i, 1) = 1
    Next i

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

    Range(Cells(2, 1), Cells(lr, lc)).Sort Key1:=Cells(2, lc), order1:=1, Header:=2
    If i > 0 Then Cells(2, lc).Resize(i).EntireRow.Delete
End Sub
 
Upvote 0
Solution
kevin9999 said:
Like
Click to expand...
kevin9999 said:
Another option.

VBA Code: 
Option Explicit
Sub AnQuala_Array()
    Dim lr As Long, lc As Long, i As Long
    Dim a, b
    lr = Cells.Find("*", , xlFormulas, , 1, 2).Row
    lc = Cells.Find("*", , xlFormulas, , 2, 2).Column + 1

    a = Range(Cells(2, 1), Cells(lr, lc))
    ReDim b(1 To UBound(a), 1 To 1)

    For i = 1 To UBound(a)
        If a(i, 2) Like "*John*" Or _
        a(i, 2) Like "*David*" Or _
        a(i, 2) Like "*Susan*" Or _
        a(i, 2) Like "*Macro*" Or _
        a(i, 11) Like "*USA*" Or _
        a(i, 11) Like "*UK*" Or _
        a(i, 11) Like "*DE*" Or _
        a(i, 11) Like "*AU*" Or _
        a(i, 11) Like "*CA*" Then b(i, 1) = 1
    Next i

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

    Range(Cells(2, 1), Cells(lr, lc)).Sort Key1:=Cells(2, lc), order1:=1, Header:=2
    If i > 0 Then Cells(2, lc).Resize(i).EntireRow.Delete
End Sub
Click to expand...
Hi can you please add sheet_name that is 'Sponsored Products' so it will know in which sheet it should perform?
 
Upvote 0
An Quala said:
Hi can you please add sheet_name that is 'Sponsored Products' so it will know in which sheet it should perform?
Click to expand...
Do you mean like this?

VBA Code: 
Option Explicit
Sub AnQuala_Array()
    Dim lr As Long, lc As Long, i As Long
    Dim a, b
    Dim ws As Worksheet: 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)

    For i = 1 To UBound(a)
        If a(i, 2) Like "*John*" Or _
        a(i, 2) Like "*David*" Or _
        a(i, 2) Like "*Susan*" Or _
        a(i, 2) Like "*Macro*" Or _
        a(i, 11) Like "*USA*" Or _
        a(i, 11) Like "*UK*" Or _
        a(i, 11) Like "*DE*" Or _
        a(i, 11) Like "*AU*" Or _
        a(i, 11) Like "*CA*" 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
 
Upvote 0
kevin9999 said:
Do you mean like this?

VBA Code: 
Option Explicit
Sub AnQuala_Array()
    Dim lr As Long, lc As Long, i As Long
    Dim a, b
    Dim ws As Worksheet: 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)

    For i = 1 To UBound(a)
        If a(i, 2) Like "*John*" Or _
        a(i, 2) Like "*David*" Or _
        a(i, 2) Like "*Susan*" Or _
        a(i, 2) Like "*Macro*" Or _
        a(i, 11) Like "*USA*" Or _
        a(i, 11) Like "*UK*" Or _
        a(i, 11) Like "*DE*" Or _
        a(i, 11) Like "*AU*" Or _
        a(i, 11) Like "*CA*" 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
Click to expand...
Yes it's working, is it possible to do other way around, for example column if column B does NOT have "Keyword" or "Product Targeting" or column K doesn't have "enabled" then delete the entire row, I think that will be more efficient.

Also can you show if I add 2 more sheet names with their set of values, where do I add that, do I copy the full code and then replace the values or within this code?

Thank you.
 
Upvote 0
Status
Not open for further replies.

Similar threads

M
  • Solved
cut/copy and paste row based on cell value not being equal to specific text
Replies
6
Views
624
mlan1604
M
B
  • Question Question
turning this vba code more dinamic
Replies
1
Views
186
NoSparks
N
C
  • Solved
Extracting data with VBA macro button from files in designated folder
2 3
Replies
20
Views
1K
Micron
M
T
  • Solved
Find #REF! errors in multiple tables and delete those rows
Replies
7
Views
310
Peter_SSs
Peter_SSs
M
  • Solved
Deleting rows from a Range variable
Replies
4
Views
364
Umma123
U

Forum statistics

Threads
1,224,817
Messages
6,181,148
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top