+ Reply to Thread
Results 1 to 7 of 7

Hide rows where multiple cells = 0

  1. #1
    Registered User
    Join Date
    06-23-2009
    Location
    New York, NY
    MS-Off Ver
    2013
    Posts
    10

    Hide rows where multiple cells = 0

    Hi,

    I am looking for a macro that will hide the rows when the cells in columns D:Z each = 0. I cannot use something that hides the row if the sum of the cells in that row = 0, because some of the cells may contain positive & negative values which cancel each other out within that row.

    I would include code, but I can't find anything on the web or on this site which I could use. Plus, I am really bad at making these up from scratch.

    Thank you,
    Vanessa
    Last edited by nessyness; 08-24-2009 at 11:03 AM.

  2. #2
    Registered User
    Join Date
    08-10-2009
    Location
    Slovakia
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Hide rows where multiple cells = 0

    I'm not sure if I understood it right, but maybe you could try something like this:


    Please Login or Register  to view this content.

  3. #3
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881

    Re: Hide rows where multiple cells = 0

    Hello Nessyness,

    The following code will hide any row in which columns D:Z all contain zeros. it uses the COUNTIF function to check for 23 zeros in those columns (23 columns).
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    06-23-2009
    Location
    New York, NY
    MS-Off Ver
    2013
    Posts
    10

    Re: Hide rows where multiple cells = 0

    Thanks to both of you guys. Roman, that one didn't seem to work for the particular thing I was trying to do. Paul, if I wanted to apply this to 5 different sheets in my workbook, so I wouldn't have to repeat it, how would I do that? Also, is there a count function that would stop when there was no further data in the row (if the column numbers had to change)?

    *This is the spreadsheet from hell

  5. #5
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Hide rows where multiple cells = 0

    Nessyness, based on Paul's original code, this adds:
    1) Applies to all sheets in workbook
    2) Only evaluates the columns D through "last column with any data in row1"
    3) Spots the last row on each sheet properly
    4) Unhides previously hidden rows to reevaluate if they should be hidden again or not

    Please Login or Register  to view this content.
    Just a note, you could probably do without the row-by-row loop on each sheet if you just entered a COUNT() or COUNTIF() formula directly on the sheets and just filtered by that column, deleting the remaining visible rows all at once. If speed isn't an issue, then the macro above will be fine.

  6. #6
    Registered User
    Join Date
    06-23-2009
    Location
    New York, NY
    MS-Off Ver
    2013
    Posts
    10

    Re: Hide rows where multiple cells = 0

    Thanks JB!

  7. #7
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Hide rows where multiple cells = 0

    If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


    (Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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