+ Reply to Thread
Results 1 to 14 of 14

Macro to hide all rows with a zero

  1. 07-26-2013, 05:36 AM #1
    CFlack8472
    CFlack8472 is offline
    Registered User
    Join Date
    10-14-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    25

    Macro to hide all rows with a zero

    Hi all

    I have been trying to find a macro which will hide any rows where the entire range in columns C:M has a value of 0. I have found a macro which comes close but it seems to hide a bit too much.

    If you see the attached sheet you will see I have highlighted some rows in Red these are rows I don't want this macro to hide and rows in Yellow which is does hide and I want it to hide.

    If anyone can help me perfect this macro so it doesn't hide the red rows I would be grateful.DSO Report Latest (version 2) static.xls

    This is the macro.

    Please Login or Register  to view this content.
    Last edited by CFlack8472; 07-26-2013 at 08:35 AM.
    Reply With QuoteRegister To Reply
  2. 07-26-2013, 05:56 AM #2
    :) Sixthsense :)
    :) Sixthsense :) is offline
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: Macro to hide all rows with a zero

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved

    If the suggestion helps you, then Click *below to Add Reputation
    Reply With QuoteRegister To Reply
  3. 07-26-2013, 05:17 PM #3
    CFlack8472
    CFlack8472 is offline
    Registered User
    Join Date
    10-14-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    25

    Re: Macro to hide all rows with a zero

    Does anyone have any ideas? I was hoping this was a fairly simple request.
    Reply With QuoteRegister To Reply
  4. 07-26-2013, 05:32 PM #4
    dberg
    dberg is offline
    Registered User
    Join Date
    07-24-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Macro to hide all rows with a zero

    Here is what I am using to do this.

    Please Login or Register  to view this content.
    I have this residing in my Sheet1 and then its automatic. Hope that helps, the only difference between mine and what you want to do is I was using a TRUE and FALSE formula to dictate what was being hidden.
    Reply With QuoteRegister To Reply
  5. 07-29-2013, 03:34 AM #5
    CFlack8472
    CFlack8472 is offline
    Registered User
    Join Date
    10-14-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    25

    Re: Macro to hide all rows with a zero

    Hi Dberg

    Thanks for that but unfortunately it didn't hide the rows I marked in red in my initial version so my original macro is still better than your one.

    Does anyone else know how to write this macro so it doesn't hide the red rows?
    Reply With QuoteRegister To Reply
  6. 07-29-2013, 03:48 AM #6
    Debraj Roy
    Debraj Roy is offline
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: Macro to hide all rows with a zero

    Hi..

    Re-Reading..
    Regards!
    =DEC2HEX(3563)

    If you like someone's answer, click the star to give them a reputation point for that answer...
    Reply With QuoteRegister To Reply
  7. 07-29-2013, 04:01 AM #7
    humdingaling
    humdingaling is offline
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Macro to hide all rows with a zero 

    Please Login or Register  to view this content.
    added if loop that overrides the value check
    Last edited by humdingaling; 07-29-2013 at 04:07 AM. Reason: ps i didnt check if your code does what its meant to i only added the color check
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
    Reply With QuoteRegister To Reply
  8. 07-29-2013, 04:05 AM #8
    AB33
    AB33 is offline
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Macro to hide all rows with a zero

    Your colouring is quite confusing.
    Row 43 is yellow- it has two blank columns. Row 56 is red and has few blank rows.
    Is it for the row to be hide every single cell in that columns(C-M) have to be empty?
    Reply With QuoteRegister To Reply
  9. 07-29-2013, 04:07 AM #9
    Debraj Roy
    Debraj Roy is offline
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: Macro to hide all rows with a zero

    with regards to AB33..

    try this..

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  10. 07-29-2013, 11:13 AM #10
    CFlack8472
    CFlack8472 is offline
    Registered User
    Join Date
    10-14-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    25

    Re: Macro to hide all rows with a zero

    Quote Originally Posted by AB33 View Post
    Your colouring is quite confusing.
    Row 43 is yellow- it has two blank columns. Row 56 is red and has few blank rows.
    Is it for the row to be hide every single cell in that columns(C-M) have to be empty?
    Hi AB33 & Debaj. Thank you for your responses. AB33 you're correct I want this to only hide a row where the entire range C to M adds up to zero.

    What I'm doing is I have a workbook with 13 reports for various departments I need to run this on a monthly basis. Some departments have zero spend in some areas so I don't need to know about rows C - M if they add up to zero. They will just take up space on the paper and cause clutter.

    The macro I posted in my first post gets 95% of the way there except it is hiding some rows which are not zeroed which I do want to see (the red ones)

    Debaraj I think your first macro again does the same thing it was hiding the red rows - do you know how to make it so it will only hide the yellow rows in my example? I want to see the red rows.
    Reply With QuoteRegister To Reply
  11. 07-29-2013, 11:38 AM #11
    AB33
    AB33 is offline
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Macro to hide all rows with a zero

    Try the attached.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  12. 07-29-2013, 07:58 PM #12
    humdingaling
    humdingaling is offline
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Macro to hide all rows with a zero

    does post #7 not exclude the red rows from being hidden?
    Reply With QuoteRegister To Reply
  13. 07-30-2013, 06:28 AM #13
    CFlack8472
    CFlack8472 is offline
    Registered User
    Join Date
    10-14-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    25

    Re: Macro to hide all rows with a zero

    Hi AB33 that works perfectly!

    I tried to edit the code so that it will do multiple worksheets. Could you post a version that will hide multiple worksheets? Otherwise this one will do.

    Quote Originally Posted by humdingaling View Post
    does post #7 not exclude the red rows from being hidden?
    Yes it did but AB33's most recent update hides only the ones I want it to hide.
    Reply With QuoteRegister To Reply
  14. 07-30-2013, 09:06 AM #14
    AB33
    AB33 is offline
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Macro to hide all rows with a zero

    Update code attached.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Macro: Copy 3 Rows & Insert below with Format....Also hide last two rows inserted
    By AWiT in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-10-2013, 12:04 PM
  2. [SOLVED] Hide rows macro
    By nickmax1 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-31-2012, 10:25 AM
  3. Using check boxes to hide and un-hide rows with drop down lists within rows
    By Sparky_Chris in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-24-2012, 07:22 AM
  4. Macro to Hide and Unhide Rows but still be able to insert rows
    By majasmi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-01-2011, 09:25 PM
  5. macro to hide rows
    By EdMac in forum Excel General
    Replies: 2
    Last Post: 02-02-2007, 08:12 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1