+ Reply to Thread
Results 1 to 16 of 16

Deleting rows totaling zero before subtotaling macro runs

  1. #1
    Registered User
    Join Date
    05-22-2018
    Location
    Bryn Mawr, Pennsylvania
    MS-Off Ver
    Excel 2013
    Posts
    12

    Deleting rows totaling zero before subtotaling macro runs

    hello,
    I have a subtotal macro, AddSub, which subtotals rows by company in a file with multiple columns. A lot of the rows are zero resulting in a lot of zero subtotals. I would like to delete these rows before the subtotal macro runs - resulting in less rows in the file. The file is created using nVision, the financial reporting section of PeopleSoft Financials using an Excel shell. I can add a formula to the Excel file, say in column Z, to total across the row. If the total in column Z, is zero for the row - I would like it deleted.
    For reference - the data begins on row 7, columns E to Q. I can add a total in Cell S7 for the preceding columns. Also, the program that creates the report will copy down this formula to each row inserted into the layout. The resulting file might be 800 rows, so the row total would be in column S for all the rows.

    For reference, the AddSub macro is below. PROD is the named range on 1 of the worksheets. The named range expands to all the lines with data after the report is created.
    I can create a second named range for the column that is the total of the preceding columns. When the total column is zero, the row should be deleted.
    I attached a file as an example. Thank you.

    Please Login or Register  to view this content.
    VBA delete rows when zero.docx
    Last edited by CStumpo; 05-24-2018 at 03:29 PM. Reason: added more details

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Deleting rows totaling zero before subtotaling macro runs

    You can edit your original post and add code tags to the code.
    -
    -
    AA CodeTags.jpg

  3. #3
    Registered User
    Join Date
    05-22-2018
    Location
    Bryn Mawr, Pennsylvania
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: Deleting rows totaling zero before subtotaling macro runs

    Done! Thank you.

  4. #4
    Registered User
    Join Date
    05-22-2018
    Location
    Bryn Mawr, Pennsylvania
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: Deleting rows totaling zero before subtotaling macro runs

    Hello,
    I am going to try this coding once our test region is back up on Tuesday. Hoping it works. The named range ZERO is the total column of all the preceding columns on that row.

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    05-22-2018
    Location
    Bryn Mawr, Pennsylvania
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: Deleting rows totaling zero before subtotaling macro runs

    Hello,
    The code to delete zero lines worked except it is not eliminating all the zero lines the first time it runs. I have had to run it 4 times. Any idea what I can change? The defined named range of Zero contains all the rows of the report, (from S6 to S1271 prior to the macro running), and changes after each run to be the last line of the report - I believe this is a good thing, I just need to have the code eliminate all the zero lines the first time around. Thank you.
    Please Login or Register  to view this content.
    Last edited by CStumpo; 05-30-2018 at 02:44 PM.

  6. #6
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Deleting rows totaling zero before subtotaling macro runs

    Is Range("Zero") a column?

    Normally if you were running a delete row column row by row, you would start at the bottom

  7. #7
    Registered User
    Join Date
    05-22-2018
    Location
    Bryn Mawr, Pennsylvania
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: Deleting rows totaling zero before subtotaling macro runs

    Hello,
    yes, range Zero is column S, row 6 to 8. This range expands to all the lines of the report after the report runs in our financial system, thus the range on my test report becomes S6 to S1121. I can see this by looking at the named range before (S6..S8) and after the report runs (S6..S1211). The last row will change week by week depending on how much data is on the report.

    I have seen coding for the bottom up delete but am not sure how to incorporate it into my macro since I am using a range. See my updated macro below.

    Is it easier to use cells or just the column? The row totals will always be in column S, could I use a cell reference like S6..S2000. (many more cells than needed).

    Please Login or Register  to view this content.

  8. #8
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Deleting rows totaling zero before subtotaling macro runs

    You can just count the rows and use that count as the loop/

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    05-22-2018
    Location
    Bryn Mawr, Pennsylvania
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: Deleting rows totaling zero before subtotaling macro runs

    Hello,
    What does Application.ScreenUpdating = False do?
    This step will be the first in a larger macro (see earlier posts).
    I will try it. Thanks.

  10. #10
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Deleting rows totaling zero before subtotaling macro runs

    Application.screenupdating=false , stops the screen from updating until the code is finished. It make a macro faster and you won't see the screen flicker.

  11. #11
    Registered User
    Join Date
    05-22-2018
    Location
    Bryn Mawr, Pennsylvania
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: Deleting rows totaling zero before subtotaling macro runs

    thanks. will it apply to the whole macro or just the one part?

  12. #12
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Deleting rows totaling zero before subtotaling macro runs


  13. #13
    Registered User
    Join Date
    05-22-2018
    Location
    Bryn Mawr, Pennsylvania
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: Deleting rows totaling zero before subtotaling macro runs

    Hello,
    1. Will try the new code in my macro but I have another question. My file has 3 other worksheets with data, will the macro effect those worksheets column S? The 2nd has data similar to the first, so it would be great if it did the zero delete as the same issue exists on that worksheet. The other 2 worksheet hold info, so I could make sure a value was in column S and they would be ignored.

    2. Considering I have a larger macro that does other things (subtotal, insert line, etc), where should the screen updating be placed? The info in the link above indicates screen updating should be set back to True at the end of the macro, would I need to do that?
    Thank you.
    Last edited by CStumpo; 05-31-2018 at 11:20 AM.

  14. #14
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Deleting rows totaling zero before subtotaling macro runs

    When the sub is finished, screenUpdating automatically is set back to true, so setting it back to true is redundant.

    You would have to indicate what sheet you want the code to delete the 0 rows.

  15. #15
    Registered User
    Join Date
    05-22-2018
    Location
    Bryn Mawr, Pennsylvania
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: Deleting rows totaling zero before subtotaling macro runs

    Hello,
    It works -except it deletes the rows 2 to 5 - which are blank and part of the report header. Non-blank rows are : Row 1 has coding, row 6 has the column S header, the data begins on row 7. How do I prevent rows 2 to 5 from being deleted? Is it possible to use the named range (Zero) which goes from S6 to the last row with data in the macro instead of just the column S?

  16. #16
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Deleting rows totaling zero before subtotaling macro runs

    Just change 2 to 6 in the code

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macro runs quick initially then bogs down after a few runs
    By pongmeister in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-19-2017, 02:02 PM
  2. Replies: 7
    Last Post: 04-09-2016, 09:22 AM
  3. Replies: 2
    Last Post: 10-10-2015, 02:22 AM
  4. [SOLVED] Macro to Hide Zero Total Rows Runs VERY Slow
    By Mr.Nick in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-18-2015, 09:55 PM
  5. [SOLVED] Macro that runs through certain range of rows
    By OceanBlue in forum Excel General
    Replies: 4
    Last Post: 10-10-2012, 05:09 PM
  6. [SOLVED] Hidding Rows Macro Runs Very Slow
    By pezalmendra in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-13-2012, 10:55 AM
  7. Subtotaling between blank rows
    By coffcons in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-07-2007, 01:20 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1