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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
like this ?
Remember that if there is a sheetname like a number, you can have unexpected results !!!
Rich (BB code): 
    For Each x In Split("Sponsored Products Campaigns|1|Sponsored Brands Campaigns|Sponsored Display Campaigns", "|")
          MsgBox "number of rows in " & x & " is " & Sheets(CStr(x)).Range("A" & Rows.Count).End(xlUp).Row - 1
     Next
 
Upvote 0
Hi, it is giving runtime error 9 subscript out of range after showing the first msgbox,

Note: I need to show all the 3 lines in the same message box like this "10 SP, 15 SB, and 0 SD Targets have been optimized" not different msgboxes,

Please check, thank you!
Rich (BB code): 
Sub counttheoptimizedrows()

   For Each x In Split("Sponsored Products Campaigns|1|Sponsored Brands Campaigns|Sponsored Display Campaigns", "|")
          MsgBox "Number of rows in " & x & " is " & Sheets(CStr(x)).Range("A" & Rows.Count).End(xlUp).Row - 1
     Next
     
     
End Sub
 
Upvote 0
i just warned you for the case your sheetnames are only numbers, that there is a risk things can go wrong.
So in the previous reactions, i inserted a number in your string of sheetnames.
If you didn't have a sheet with the name "1", that 'll cause an error, so delete that number or add a sheet "1"
VBA Code: 
     For Each x In Split("Sponsored Products Campaigns|Sponsored Brands Campaigns|1|Sponsored Display Campaigns", "|")
          s = s & IIf(Len(s) = 0, "", vbLf) & "number of rows in " & x & " is " & Sheets(CStr(x)).Range("A" & Rows.Count).End(xlUp).Row - 1
          MsgBox s, vbInformation, "temporary msgbox"           '-----> only now for demonstration, DELETE LATER
     Next

     MsgBox s, vbExclamation, "the definitive one"
 
Upvote 0
i just warned you for the case your sheetnames are only numbers, that there is a risk things can go wrong.
So in the previous reactions, i inserted a number in your string of sheetnames.
If you didn't have a sheet with the name "1", that 'll cause an error, so delete that number or add a sheet "1"
Click to expand...
Hi, I donot have any sheet named as numbers, so this is not relevant for me, I only have 4 sheets named as 'Sponsored Products Campaigns', 'Sponsored Brands Campaigns', 'Sponsored Display Campaigns', and 'Control Panel', and I donot have any other sheet please.
 
Upvote 0
then you could delete that number and add that extra "Control Panel"
That extra msgbox within the for...next loop, you can also delete that one (once you understand the code).

The code in previous reaction showed you 2 intermediate messages before stopping because of an error, isn't it ?
VBA Code: 
For Each x In Split("Sponsored Products Campaigns|Sponsored Brands Campaigns|Sponsored Display Campaigns|Control Panel", "|")
          s = s & IIf(Len(s) = 0, "", vbLf) & "number of rows in " & x & " is " & Sheets(CStr(x)).Range("A" & Rows.Count).End(xlUp).Row - 1
          MsgBox s, vbInformation, "temporary msgbox"           '-----> only now for demonstration, DELETE LATER
     Next

     MsgBox s, vbExclamation, "the definitive one"
 
Upvote 0
Hi thank you,

There are few issues with this please,

1- It is showing 5 msgboxes in total, I need only 1 box with the complete information which kind of in the last box.
2- I don't need information for control panel, only for the first 3 sheets

Thank you.
 
Upvote 0
BSALV said:
then you could delete that number and add that extra "Control Panel"
That extra msgbox within the for...next loop, you can also delete that one (once you understand the code).
Click to expand...
i already gave you the answer in #6, once you saw how it works, you could delete the msgbox in the for...next loop and i added "Control Panel" after your request in #5.

VBA Code: 
      For Each x In Split("Sponsored Products Campaigns|Sponsored Brands Campaigns|Sponsored Display Campaigns", "|")
          s = s & IIf(Len(s) = 0, "", vbLf) & "number of rows in " & x & " is " & Sheets(CStr(x)).Range("A" & Rows.Count).End(xlUp).Row - 1
      Next

     MsgBox s
 
Upvote 0
Hi, there are 5 msgboxes showing, instead of just one, can you please delete the 4 msgboxes and just show me the last one, Also no need to count for 'Control Panel', so last one would be the fourth one, can you please do that?
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Solved
Looking For Tips To Improve My VBA
Replies
8
Views
198
who am i
W
G
  • Solved
Skip Row When Column Word Meet
2
Replies
14
Views
358
Peter_SSs
Peter_SSs
D
  • Solved
Dynamic AutoFill
2
Replies
18
Views
205
MARK858
MARK858
J
  • Question Question
Copying Rows Based on Column Value
Replies
1
Views
80
jolivanes
jolivanes
J
  • Question Question
VBA removing heading in spreadsheet
Replies
3
Views
264
rlv01
R

Forum statistics

Threads
1,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

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