An Quala
Board Regular
- Joined
- Mar 21, 2022
- Messages
- 146
- Office Version
- 2021
- Platform
- Windows
Hi, I want to count the number of rows - header in each sheet after the code has completed and add in the message box, there are 3 sheets in total, and after running the code one could be empty so in that case it would be 0
Example:
"10 SP, 15 SB, and 0 SD Targets have been optimized"
where,
SP = No of rows in 'Sponsored Products Campaigns' minus Header
SB = No of rows in 'Sponsored Brands Campaigns' minus Header
SD = No of rows in 'Sponsored Display Campaigns' minus Header
Example:
"10 SP, 15 SB, and 0 SD Targets have been optimized"
where,
SP = No of rows in 'Sponsored Products Campaigns' minus Header
SB = No of rows in 'Sponsored Brands Campaigns' minus Header
SD = No of rows in 'Sponsored Display Campaigns' minus Header
VBA Code:
Sub workingp()
Dim dStart As Double
Dim dTime As Double
Dim Answer As VbMsgBoxResult
Answer = MsgBox("Are you sure you want to run this program?", vbYesNo, "Execute Confirmation")
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
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)
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)")
On Error Resume Next
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
.ClearContents
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)"
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)
With .Offset(1).Resize(.Rows.Count - 1).Columns(45)
.Value = .Parent.Evaluate("=IF((" & .Offset(, -42).Address & "<>""update""),"""",1)")
On Error Resume Next
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
.ClearContents
End With
End With
Range("A1:AR" & Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter
'Sponsored Brands Campaigns
Sheets("Sponsored Brands Campaigns").Select
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)
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)")
On Error Resume Next
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
.ClearContents
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)
With .Offset(1).Resize(.Rows.Count - 1).Columns(49)
.Value = .Parent.Evaluate("=IF((" & .Offset(, -46).Address & "<>""update""),"""",1)")
On Error Resume Next
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
.ClearContents
End With
End With
Range("A1:AV" & Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter
'Sponsored Display Campaigns
Sheets("Sponsored Display Campaigns").Select
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)
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)")
On Error Resume Next
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
.ClearContents
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)
With .Offset(1).Resize(.Rows.Count - 1).Columns(43)
.Value = .Parent.Evaluate("=IF((" & .Offset(, -40).Address & "<>""update""),"""",1)")
On Error Resume Next
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
.ClearContents
End With
End With
Range("A1:AP" & Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter
dTime = Timer - dStart
MsgBox "All targets have been optimized in " & Format(dTime, "0.0") & " seconds."
End If
End Sub