VBA Code Mistake

An Quala

An Quala

Board Regular
Joined
Mar 21, 2022
Messages
146
Office Version
  1. 2021
Platform
  1. Windows
Hello, can anyone please help me find out what is the mistake in this VBA Code, it is not giving any error but highlighted part is not doing what it needs to do (deleting the rows as per the mentioned criteria)

Rich (BB code): 
Sub Xlookup()

    Dim c As Range, va, x
     For Each x In Split("Sponsored Products|Sponsored Brands|Sponsored Display", "|")
        Set c = Worksheets(x).UsedRange
        va = c.Value
        Worksheets(x).Cells.NumberFormat = "General"
        c = va
    Next
    
    Sheets("Sponsored Products").Select
    
    Columns("D:G").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    
    Columns("Y:Y").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

    Range("G2").FormulaR1C1 = "=XLOOKUP(RC[1],R2C8:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C8,R2C10:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C10)"
    
    Range("G2").AutoFill Destination:=Range("G2:G" & Cells(Rows.Count, 1).End(xlUp).Row)
    
        Columns("G:G").Copy
    Columns("G:G").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

        
         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 "*Campaign*" Or _
        a(i, 2) Like "*Ad Group*" Or _
        a(i, 2) Like "*Bidding Adjustment*" Or _
        a(i, 2) Like "*Product Ad*" Or _
        a(i, 11) Like "*Negative Keyword*" Or _
        a(i, 19) Like "*paused*" Or _
        a(i, 46) Like "*paused*" Or _
        a(i, 47) Like "*paused*" Then b(i, 1) = 1
    Next i
End Sub



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 ProductsCampaign51810182230962Portfolio1abc20220131MANUALenabled100.0Dynamic bids - down only2739100.37%5.00.0000.00.0%0.50.0abcabcenabled
3Sponsored ProductsBidding Adjustment51810182230962Dynamic bids - down onlyplacementProductPage0.0248420.08%0.90.0000.00.0%0.450.0abcabcenabled
4Sponsored ProductsBidding Adjustment51810182230962Dynamic bids - down onlyplacementTop0.011686.9%4.10.0000.00.0%0.510.0abcabcenabled
5Sponsored ProductsAd Group51810182230962103843358501771abcenabled0.42739100.37%5.00.0000.00.0%0.50.0abcabcenabledenabled
6Sponsored ProductsProduct Ad51810182230962103843358501771187095918266508enabledabc2739100.37%5.00.0000.00.0%0.50.0abcabcenabledenabled
7Sponsored ProductsKeyword51810182230962103843358501771133091597391670enabled0.58abcbroad000.0%0.00.0000.00.0%0.00.0abcabcenabledenabled0.4
8Sponsored ProductsKeyword5181018223096210384335850177120484689160127enabled0.64abcbroad2085100.48%5.00.0000.00.0%0.50.0abcabcenabledenabled0.4
9Sponsored ProductsKeyword51810182230962103843358501771147161619594891enabled0.58abcbroad2300.0%0.00.0000.00.0%0.00.0abcabcenabledenabled0.4
10Sponsored ProductsKeyword51810182230962103843358501771117074780596747enabled0.62abcbroad39400.0%0.00.0000.00.0%0.00.0abcabcenabledenabled0.4
11Sponsored ProductsKeyword51810182230962103843358501771198054310782002enabled0.64abcbroad000.0%0.00.0000.00.0%0.00.0abcabcenabledenabled0.4
12Sponsored ProductsKeyword51810182230962103843358501771216796694767871enabled0.64abcbroad23100.0%0.00.0000.00.0%0.00.0abcabcenabledenabled0.4
13Sponsored ProductsKeyword51810182230962103843358501771116800394856451enabled0.64abcbroad600.0%0.00.0000.00.0%0.00.0abcabcenabledenabled0.4
14Sponsored ProductsCampaign224654867824700Portfolio1abc20220101MANUALenabled100.0abcDynamic bids - down only4800.0%0.00.0000.00.0%0.00.0abcabcenabled
15Sponsored ProductsBidding Adjustment224654867824700abcDynamic bids - down onlyplacementProductPage0.03900.0%0.00.0000.00.0%0.00.0abcabcenabled
16Sponsored ProductsBidding Adjustment224654867824700abcDynamic bids - down onlyplacementTop0.0000.0%0.00.0000.00.0%0.00.0abcabcenabled
17Sponsored ProductsAd Group224654867824700186267109368336abcenabled1.1abc4800.0%0.00.0000.00.0%0.00.0abcabcenabledenabled
18Sponsored ProductsProduct Ad22465486782470018626710936833652184089599015enabledabcabc4800.0%0.00.0000.00.0%0.00.0abcabcenabledenabled
19Sponsored ProductsKeyword22465486782470018626710936833683911309498353enabled1.08abcexact000.0%0.00.0000.00.0%0.00.0abcabcenabledenabled1.1
20Sponsored ProductsKeyword22465486782470018626710936833653354652434783enabled1.08abcexact000.0%0.00.0000.00.0%0.00.0abcabcenabledenabled1.1
21Sponsored ProductsKeyword2246548678247001862671093683362712747284010enabled0.97abcexact3400.0%0.00.0000.00.0%0.00.0abcabcenabledenabled1.1
22Sponsored ProductsKeyword2246548678247001862671093683368880509831632enabled0.97abcexact200.0%0.00.0000.00.0%0.00.0abcabcenabledenabled1.1
23Sponsored ProductsKeyword22465486782470018626710936833629871655919998enabled1.08abcexact100.0%0.00.0000.00.0%0.00.0abcabcenabledenabled1.1
24Sponsored ProductsKeyword224654867824700186267109368336207922361834615enabled1.57abcexact1000.0%0.00.0000.00.0%0.00.0abcabcenabledenabled1.1
25Sponsored ProductsKeyword22465486782470018626710936833682552476516641enabled0.8abcexact000.0%0.00.0000.00.0%0.00.0abcabcenabledenabled1.1
26Sponsored ProductsKeyword224654867824700186267109368336125041523559069enabled1.08abcexact000.0%0.00.0000.00.0%0.00.0abcabcenabledenabled1.1
27Sponsored ProductsKeyword224654867824700186267109368336178224892278959enabled1.17abcexact000.0%0.00.0000.00.0%0.00.0abcabcenabledenabled1.1
28Sponsored ProductsKeyword224654867824700186267109368336237702370532591enabled1.25abcexact100.0%0.00.0000.00.0%0.00.0abcabcenabledenabled1.1
29Sponsored ProductsCampaign60228050322464Portfolio1abc20211221MANUALenabled100.0abcDynamic bids - down only100.0%0.00.0000.00.0%0.00.0abcabcenabled
30Sponsored ProductsBidding Adjustment60228050322464abcDynamic bids - down onlyplacementProductPage0.0100.0%0.00.0000.00.0%0.00.0abcabcenabled
31Sponsored ProductsBidding Adjustment60228050322464abcDynamic bids - down onlyplacementTop0.0000.0%0.00.0000.00.0%0.00.0abcabcenabled
32Sponsored ProductsAd Group60228050322464225369445700841abcenabled0.06abc100.0%0.00.0000.00.0%0.00.0abcabcenabledenabled
33Sponsored ProductsProduct Ad60228050322464225369445700841101252162650765enabledabcabc100.0%0.00.0000.00.0%0.00.0abcabcenabledenabled
34Sponsored ProductsKeyword60228050322464225369445700841232179385161330enabled0.11abcphrase100.0%0.00.0000.00.0%0.00.0abcabcenabledenabled0.06
35Sponsored ProductsKeyword60228050322464225369445700841255771627511436enabled0.12abcphrase000.0%0.00.0000.00.0%0.00.0abcabcenabledenabled0.06
36Sponsored ProductsKeyword60228050322464225369445700841176928454042161enabled0.12abcphrase000.0%0.00.0000.00.0%0.00.0abcabcenabledenabled0.06
37Sponsored ProductsKeyword6022805032246422536944570084181841757906585enabled0.12abcphrase000.0%0.00.0000.00.0%0.00.0abcabcenabledenabled0.06
38Sponsored ProductsKeyword6022805032246422536944570084180654821572179enabled0.12abcphrase000.0%0.00.0000.00.0%0.00.0abcabcenabledenabled0.06
39Sponsored ProductsKeyword60228050322464225369445700841273551451104575enabled0.12abcphrase000.0%0.00.0000.00.0%0.00.0abcabcenabledenabled0.06
40Sponsored ProductsKeyword6022805032246422536944570084140001743584860enabled0.12abcphrase000.0%0.00.0000.00.0%0.00.0abcabcenabledenabled0.06
41Sponsored ProductsKeyword60228050322464225369445700841259424722839019enabled0.12abcphrase000.0%0.00.0000.00.0%0.00.0abcabcenabledenabled0.06
42Sponsored ProductsKeyword60228050322464225369445700841278118504947134enabled0.12abcphrase000.0%0.00.0000.00.0%0.00.0abcabcenabledenabled0.06
43Sponsored ProductsKeyword6022805032246422536944570084198753070646170enabled0.12abcphrase000.0%0.00.0000.00.0%0.00.0abcabcenabledenabled0.06
44Sponsored ProductsCampaign103977923004034Portfolio1abc20220201MANUALenabled100.0abcDynamic bids - down only57610.17%0.370.0000.00.0%0.370.0abcabcenabled
45Sponsored ProductsBidding Adjustment103977923004034abcDynamic bids - down onlyplacementProductPage0.050110.2%0.370.0000.00.0%0.370.0abcabcenabled
46Sponsored ProductsBidding Adjustment103977923004034abcDynamic bids - down onlyplacementTop0.0100.0%0.00.0000.00.0%0.00.0abcabcenabled
47Sponsored ProductsAd Group103977923004034221037315953408abcenabled0.4abc57610.17%0.370.0000.00.0%0.370.0abcabcenabledenabled
48Sponsored ProductsProduct Ad10397792300403422103731595340849309464963870enabledabcabc57610.17%0.370.0000.00.0%0.370.0abcabcenabledenabled
49Sponsored ProductsProduct Targeting103977923004034221037315953408186561934932770enabled0.48abccategory="6292252011"57610.17%0.370.0000.00.0%0.370.0abcabcenabledenabled0.4category="Basic Dog Leashes"
50Sponsored ProductsCampaign231897683081825Portfolio1abc20190918MANUALenabled500.0abcFixed bid11730460.39%35.2178.87330.0744.64%0.772.24abcabcenabled
51Sponsored ProductsBidding Adjustment231897683081825abcFixed bidplacementProductPage0.010091110.11%8.0352.58220.1815.27%0.736.55abcabcenabled
52Sponsored ProductsBidding Adjustment231897683081825abcFixed bidplacementTop0.0609264.27%19.2826.29110.0473.34%0.741.36abcabcenabled
53Sponsored ProductsAd Group23189768308182564716315089601abcenabled0.35abc11730460.39%35.2178.87330.0744.64%0.772.24abcabcenabledenabled
54Sponsored ProductsProduct Ad23189768308182564716315089601231724180855057enabledabcabc000.0%0.00.0000.00.0%0.00.0abcabcenabledenabled
55Sponsored ProductsProduct Ad23189768308182564716315089601148589570862064enabledabcabc164300.0%0.00.0000.00.0%0.00.0abcabcenabledenabled
56Sponsored ProductsProduct Ad23189768308182564716315089601198459992972544enabledabcabc5431290.53%21.3952.58220.0740.68%0.742.46abcabcenabledenabled
57Sponsored ProductsProduct Ad23189768308182564716315089601159962242985555enabledabcabc2006100.5%8.480.0000.00.0%0.850.0abcabcenabledenabled
58Sponsored ProductsProduct Ad2318976830818256471631508960186795561866493enabledabcabc265070.26%5.3426.29110.1420.31%0.764.92abcabcenabledenabled
59Sponsored ProductsKeyword23189768308182564716315089601107190882986enabled1.08abcexact000.0%0.00.0000.00.0%0.00.0abcabcenabledenabled0.35
60Sponsored ProductsKeyword23189768308182564716315089601250229049391726enabled1.08abcexact000.0%0.00.0000.00.0%0.00.0abcabcenabledenabled0.35
61Sponsored ProductsKeyword23189768308182564716315089601159629857812834enabled0.47abcexact000.0%0.00.0000.00.0%0.00.0abcabcenabledenabled0.35
62Sponsored ProductsKeyword2318976830818256471631508960199621606761191enabled0.97abcexact1900.0%0.00.0000.00.0%0.00.0abcabcenabledenabled0.35
63Sponsored ProductsKeyword23189768308182564716315089601262282056477120enabled1.08abcexact000.0%0.00.0000.00.0%0.00.0abcabcenabledenabled0.35
64Sponsored ProductsKeyword23189768308182564716315089601180153277825366enabled1.43abcexact2300.0%0.00.0000.00.0%0.00.0abcabcenabledenabled0.35
65Sponsored ProductsKeyword2318976830818256471631508960131116908056465enabled0.8abcexact000.0%0.00.0000.00.0%0.00.0abcabcenabledenabled0.35
66Sponsored ProductsKeyword2318976830818256471631508960118589949919533enabled1.08abcexact000.0%0.00.0000.00.0%0.00.0abcabcenabledenabled0.35
67Sponsored ProductsKeyword23189768308182564716315089601267732104914794enabled1.17abcexact200.0%0.00.0000.00.0%0.00.0abcabcenabledenabled0.35
68Sponsored ProductsKeyword2318976830818256471631508960117177174734648enabled1.17abcexact13300.0%0.00.0000.00.0%0.00.0abcabcenabledenabled0.35
Sheet1
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
An Quala said:
deleting the rows as per the mentioned criteria
Click to expand...
There is no code that deletes rows. This code is looking at one array, and if it matches any of several text patterns, it puts a 1 in a corresponding array.

Can you describe in more detail what you want to happen?
 
Upvote 0
@An Quala As I see it, that red portion is populating b correctly with 1's but you have no subsequent code that is doing any deleting.
 
Upvote 0
Thank you guys I have found the mistake,

Here's the code:

VBA Code: 
Option Explicit
Sub Xlookup()

    Dim c As Range, va, x
     For Each x In Split("Sponsored Products|Sponsored Brands|Sponsored Display", "|")
        Set c = Worksheets(x).UsedRange
        va = c.Value
        Worksheets(x).Cells.NumberFormat = "General"
        c = va
    Next
    
    Sheets("Sponsored Products").Select
    
    Columns("D:G").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    
    Columns("Y:Y").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

    Range("G2").FormulaR1C1 = "=XLOOKUP(RC[1],R2C8:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C8,R2C10:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C10)"
    
    Range("G2").AutoFill Destination:=Range("G2:G" & Cells(Rows.Count, 1).End(xlUp).Row)
    
        Columns("G:G").Copy
    Columns("G:G").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
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 "*Campaign*" Or _
        a(i, 2) Like "*Ad Group*" Or _
        a(i, 2) Like "*Bidding Adjustment*" Or _
        a(i, 2) Like "*Product Ad*" Or _
        a(i, 11) Like "*Negative Keyword*" Or _
        a(i, 19) Like "*paused*" Or _
        a(i, 46) Like "*paused*" Or _
        a(i, 47) Like "*paused*" 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
Solution
If it works, that's great, but it seems a little tortuous to load to an array, mark them in another array, write the second array back, sort, and delete. I would be inclined to simplify the approach and simply work backwards through the rows and delete them as I go without using the arrays, unless this is a massive amount of data (>100,000 rows)
 
Upvote 0
Hey @6StringJazzer, I don't know much about VBA, this code was given by a programmer, but yes rows could be 50k - 500k, so if you have better way to achieve the same result then please suggest that, I am looking to add 2 more things,

1st: I would reverse the criteria means I would delete all rows except column B = "Keyword" or "Product Targeting" AND column S or AS or AT = "enabled"

2nd: I would like to do the same thing for 2 other sheets as well but column # will be different for them, for now I am just copy pasting this code three times.

Thank you.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
vba code to give next Sunday date for pasted data
Replies
2
Views
702
bh24524
B
F
  • Solved
Excel formula needed
6 7 8
Replies
79
Views
4K
KRice
K
C
  • Solved
Macro/VBA for lookups
Replies
9
Views
183
mumps
mumps
B
  • Question Question
Suggestions for Building a Historical Cost Database by Project
Replies
0
Views
98
babyjwhale
B
M
  • Question Question
VBA Subtotal 47,000+ Rows
Replies
1
Views
259
igold
igold

Forum statistics

Threads
1,224,820
Messages
6,181,154
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