Sub Bulk_OptimizeV2()
Dim dStart As Double
Dim dTime As Double
Dim Answer As VbMsgBoxResult
Answer = MsgBox("Are you sure you want to run this program?", vbYesNo, "Confirmation Message")
If Answer = vbYes Then
dStart = Timer
Dim c As Range, va, x
For Each x In Split("Sponsored Products Campaigns|Sponsored Brands Campaigns|Sponsored Display Campaigns", "|")
Set c = Worksheets(x).UsedRange
va = c.Value
Worksheets(x).Cells.NumberFormat = "General"
c = va
Next
'Sponsored Products Campaigns
Sheets("Sponsored Products Campaigns").Select
If Range("A1") <> "" Then
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
With Sheets("Sponsored Products Campaigns").Cells(1).CurrentRegion.Resize(, 49)
On Error Resume Next
With .Offset(1).Resize(.Rows.Count - 1).Columns(49)
.Value = .Parent.Evaluate("=IF((" & .Offset(, -47).Address & "<>""Keyword"")*(" & .Offset(, -47).Address & "<>""Product Targeting"")+(" & .Offset(, -30).Address & "<>""enabled"")+(" & .Offset(, -4).Address & "<>""enabled"")+(" & .Offset(, -3).Address & "<>""enabled""),"""",1)")
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
.ClearContents
On Error GoTo 0
End With
End With
Sheets("Control Panel").Select
If Range("H42") = "Turn On" Then
Sheets("Sponsored Products Campaigns").Select
Application.ScreenUpdating = False
With ActiveSheet
Set vRng = .UsedRange.Columns("AQ")
vRngJ42 = Sheets("Control Panel").Range("J42").Value
vRows = vRng.Rows.Count
For vN = vRows To 1 Step -1
If InStr(1, UCase(.Cells(vN, "AQ")), UCase(vRngJ42)) Then _
.Rows(vN).EntireRow.Delete
Next vN
End With
Application.ScreenUpdating = True
End If
Sheets("Sponsored Products Campaigns").Select
Range("F2").FormulaR1C1 = _
"=XLOOKUP(RC7,'Control Panel'!R9C4:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C4,'Control Panel'!R9C5:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C5)"
Range("F2").AutoFill Destination:=Range("F2:F" & Cells(Rows.Count, 1).End(xlUp).Row)
Sheets("Control Panel").Select
Range("D2:E2").Copy
Sheets("Sponsored Products Campaigns").Select
Range("D2").Select
ActiveSheet.Paste
Range("C2").Select
ActiveCell.FormulaR1C1 = "=IF(RC5<>"""",""update"","""")"
Range("C2:E2").AutoFill Destination:=Range("C2:E" & Cells(Rows.Count, 1).End(xlUp).Row)
Range("Y1").NumberFormat = "General"
Range("Y1").FormulaR1C1 = "Bid Change %"
Range("Y2").FormulaR1C1 = "=IFERROR((RC4-RC24)/RC24,"""")"
Range("Y2").Style = "Percent"
Range("Y2").AutoFill Destination:=Range("Y2:Y" & Cells(Rows.Count, 1).End(xlUp).Row)
Columns("Y:Y").Copy
Columns("Y:Y").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("C:F").Copy
Columns("C:F").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("F:G").Delete Shift:=xlToLeft
Range("D2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Range("V2").Select
ActiveSheet.Paste
Range("E2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Range("Q2").Select
ActiveSheet.Paste
Columns("D:E").Delete Shift:=xlToLeft
With Sheets("Sponsored Products Campaigns").Cells(1).CurrentRegion.Resize(, 45)
On Error Resume Next
With .Offset(1).Resize(.Rows.Count - 1).Columns(45)
.Value = .Parent.Evaluate("=IF((" & .Offset(, -42).Address & "<>""update""),"""",1)")
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
.ClearContents
On Error GoTo 0
End With
End With
Range("A1:AR" & Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter
End If
'Sponsored Brands Campaigns
Sheets("Sponsored Brands Campaigns").Select
If Range("A1") <> "" Then
Columns("D:G").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("X:X").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
With Sheets("Sponsored Brands Campaigns").Cells(1).CurrentRegion.Resize(, 54)
On Error Resume Next
With .Offset(1).Resize(.Rows.Count - 1).Columns(54)
.Value = .Parent.Evaluate("=IF((" & .Offset(, -52).Address & "<>""Keyword"")*(" & .Offset(, -52).Address & "<>""Product Targeting"")+(" & .Offset(, -36).Address & "<>""running"")*(" & .Offset(, -36).Address & "<>""other"")+(" & .Offset(, -37).Address & "<>""enabled"")+(" & .Offset(, -3).Address & "<>""enabled""),"""",1)")
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
.ClearContents
On Error GoTo 0
End With
End With
Range("F2").FormulaR1C1 = _
"=XLOOKUP(RC7,'Control Panel'!R9C4:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C4,'Control Panel'!R9C5:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C5)"
Sheets("Control Panel").Select
Range("D4:E4").Copy
Sheets("Sponsored Brands Campaigns").Select
Range("D2").Select
ActiveSheet.Paste
Range("C2").Select
ActiveCell.FormulaR1C1 = "=IF(RC5<>"""",""update"","""")"
Range("C2:F2").AutoFill Destination:=Range("C2:F157")
Range("X2").FormulaR1C1 = "=IFERROR((RC4-RC23)/RC23,"""")"
Range("X2").Style = "Percent"
Range("X1").NumberFormat = "General"
Range("X1").FormulaR1C1 = "Bid Change %"
Range("X2").AutoFill Destination:=Range("X2:X" & Cells(Rows.Count, 1).End(xlUp).Row)
Columns("X:X").Copy
Columns("X:X").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("C:F").Copy
Columns("C:F").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("F:G").Delete Shift:=xlToLeft
Range("D2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Range("U2").Select
ActiveSheet.Paste
Range("E2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Range("O2").Select
ActiveSheet.Paste
Columns("D:E").Delete Shift:=xlToLeft
With Sheets("Sponsored Brands Campaigns").Cells(1).CurrentRegion.Resize(, 49)
On Error Resume Next
With .Offset(1).Resize(.Rows.Count - 1).Columns(49)
.Value = .Parent.Evaluate("=IF((" & .Offset(, -46).Address & "<>""update""),"""",1)")
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
.ClearContents
On Error GoTo 0
End With
End With
Range("A1:AV" & Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter
End If
'Sponsored Display Campaigns
Sheets("Sponsored Display Campaigns").Select
If Range("A1") <> "" Then
Columns("D:G").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("Y:Z").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("G2").FormulaR1C1 = "=XLOOKUP(RC[1],R2C8:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C8,R2C9:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C9)"
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
With Sheets("Sponsored Display Campaigns").Cells(1).CurrentRegion.Resize(, 48)
On Error Resume Next
With .Offset(1).Resize(.Rows.Count - 1).Columns(48)
.Value = .Parent.Evaluate("=IF((" & .Offset(, -46).Address & "<>""Audience Targeting"")*(" & .Offset(, -46).Address & "<>""Product Targeting"")+(" & .Offset(, -31).Address & "<>""enabled"")+(" & .Offset(, -5).Address & "<>""enabled"")+(" & .Offset(, -4).Address & "<>""enabled""),"""",1)")
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
.ClearContents
On Error GoTo 0
End With
End With
Range("Z2").FormulaR1C1 = "=IF(RC24="""",RC45,RC24)"
Range("Z2").AutoFill Destination:=Range("Z2:Z" & Cells(Rows.Count, 1).End(xlUp).Row)
Columns("Z:Z").Copy
Columns("X:X").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("X1").Select
Selection.NumberFormat = "General"
ActiveCell.FormulaR1C1 = "Bid"
Columns("Z:Z").Delete Shift:=xlToLeft
Range("F2").FormulaR1C1 = _
"=XLOOKUP(RC7,'Control Panel'!R9C4:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C4,'Control Panel'!R9C5:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C5)"
Range("F2").AutoFill Destination:=Range("F2:F" & Cells(Rows.Count, 1).End(xlUp).Row)
Sheets("Control Panel").Select
Range("D6:E6").Copy
Sheets("Sponsored Display Campaigns").Select
Range("D2").Select
ActiveSheet.Paste
Range("C2").FormulaR1C1 = "=IF(RC5<>"""",""update"","""")"
Range("C2:E2").AutoFill Destination:=Range("C2:E" & Cells(Rows.Count, 1).End(xlUp).Row)
Range("C2:E" & Cells(Rows.Count, 1).End(xlUp).Row).Select
Range("Y1").NumberFormat = "General"
Range("Y1").FormulaR1C1 = "Bid Change %"
Range("Y2").FormulaR1C1 = "=IFERROR((RC4-RC24)/RC24,"""")"
Range("Y2").Style = "Percent"
Range("Y2").AutoFill Destination:=Range("Y2:Y" & Cells(Rows.Count, 1).End(xlUp).Row)
Columns("Y:Y").Copy
Columns("Y:Y").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("C:F").Copy
Columns("C:F").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("F:G").Delete Shift:=xlToLeft
Range("D2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Range("V2").Select
ActiveSheet.Paste
Range("E2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Range("O2").Select
ActiveSheet.Paste
Columns("D:E").Delete Shift:=xlToLeft
With Sheets("Sponsored Display Campaigns").Cells(1).CurrentRegion.Resize(, 43)
On Error Resume Next
With .Offset(1).Resize(.Rows.Count - 1).Columns(43)
.Value = .Parent.Evaluate("=IF((" & .Offset(, -40).Address & "<>""update""),"""",1)")
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
.ClearContents
On Error GoTo 0
End With
End With
Range("A1:AP" & Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter
End If
dTime = Timer - dStart
For Each x In Split("Sponsored Products Campaigns|Sponsored Brands Campaigns|Sponsored Display Campaigns", "|")
s = s & Sheets(CStr(x)).Range("A" & Rows.Count).End(xlUp).Row - 1 & " " 'collect the last rows and use the space as separator
Next
sp = Split(s) 'split string on space
MsgBox sp(0) & " SP, " & sp(1) & " SB and " & sp(2) & " SD targets have been optimized in " & Format(dTime, "0.0") & "seconds." 'make the text
With ThisWorkbook.Sheets(Array("Sponsored Products Campaigns", "Sponsored Display Campaigns", "Sponsored Brands Campaigns"))
.Copy
End With
With ActiveWorkbook
.Worksheets("Sponsored Products Campaigns").Range("U:U,AB:AR").EntireColumn.Delete
.Worksheets("Sponsored Brands Campaigns").Range("T:T,AI:AW").EntireColumn.Delete
.Worksheets("Sponsored Display Campaigns").Range("U:U,Y:AO").EntireColumn.Delete
End With
If MsgBox("Columns have been deleted" & vbNewLine & vbNewLine & _
"Do you want to save as a new file?", vbYesNo, "Confirm") = vbNo Then
ActiveWorkbook.Close SaveChanges:=0
Exit Sub
End If
Dim FileName As Variant
FileName = Application.GetSaveAsFilename(FileFilter:="Microsoft Excel file (*.xlsx), *.xlsx")
ActiveWorkbook.SaveAs FileName:=FileName
End If
End Sub