Count the number of rows present in the sheets and put them in the msgbox

An Quala

An Quala

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


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
 
Sort by date Sort by votes

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Hi yes somehow the new one didn't save and old one was running, so the #8 is okay, but can I get only line showing all 3 values, for example, "(64) SP, (24) SB and (39) SD targets have been optimized"

Thank you
 
Upvote 0
VBA Code: 
     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"     'make the text
 
Upvote 0
You must log in or register to reply here.

Similar threads

An Quala
  • Question
Check if there's data in a sheet before executing the code, if not then jump to the next sheet
2
Replies
11
Views
1K
offthelip
O
L
  • Locked
  • Question
Help Cleaning Up Code
Replies
2
Views
331
RoryA
RoryA
N
  • Question
My code is suddenly really slow
Replies
1
Views
134
RobP
R
F
  • Question
Conditional formatting of a variable range
Replies
0
Views
60
Flavien
F
K
  • Question
Change code from inside sheet to userform directly
Replies
0
Views
160
KalilMe
K

Forum statistics

Threads
1,214,938
Messages
6,122,346
Members
449,080
Latest member
Armadillos

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