+ Reply to Thread
Results 1 to 13 of 13

Hide blank Rows - non text/color

  1. 12-28-2009, 06:07 PM #1
    jeff.p
    jeff.p is offline
    Registered User
    Join Date
    08-23-2007
    Posts
    58

    Hide blank Rows - non text/color

    Hello,

    I received this code which hides blank rows within a range.

    Please Login or Register  to view this content.

    It works great.

    Is there a way to modify this to also include cells containing color?
    ie, if a cell doesn't contains text or color, within a range, to hide those blank rows?

    Thanks for any help

    Jeff
    Reply With QuoteRegister To Reply
  2. 12-28-2009, 06:30 PM #2
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Hide blank Rows - non text/color

    Hello Jeff,

    I am assuming you mean the background color of the cell. It depends on how this was set. If was set manually or through VBA code then it is easy. If it was changed using Conditional Formatting then the coding is much more complex.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)
    Reply With QuoteRegister To Reply
  3. 12-28-2009, 06:53 PM #3
    jeff.p
    jeff.p is offline
    Registered User
    Join Date
    08-23-2007
    Posts
    58

    Re: Hide blank Rows - non text/color

    Thanks, Leith

    That's correct, the background color of a cell, which were formatted via VBA. (not conditional format)

    thanks
    Last edited by jeff.p; 12-28-2009 at 06:55 PM.
    Reply With QuoteRegister To Reply
  4. 12-28-2009, 07:05 PM #4
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Hide blank Rows - non text/color

    Hell Jeff,

    This change should to do it. The CountA function will tell if the any of the cells have text or formulae. The IsNull test will be true when any cell has color different from the worksheet default color.
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 12-28-2009, 07:10 PM #5
    jabryantiii
    jabryantiii is offline
    Registered User
    Join Date
    12-28-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    98

    Re: Hide blank Rows - non text/color

    is it possible to code that to a button so that when you click is hides a given range?
    Reply With QuoteRegister To Reply
  6. 12-28-2009, 07:11 PM #6
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Hide blank Rows - non text/color

    The IsNull test will be true when any cell has color different from the worksheet default color.
    When all cells are not the same color, regardless of what color.
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  7. 12-28-2009, 08:54 PM #7
    jeff.p
    jeff.p is offline
    Registered User
    Join Date
    08-23-2007
    Posts
    58

    Re: Hide blank Rows - non text/color

    Thanks, Leith

    Sorry, but i cant seem to get this to work,

    I created a new worksheet, set up a simple range with a few numbers & letters in various cells, leaving others blank. and ran the code... which works in that it hid all the rows containing blank cells within the range.

    I added a cell background color in a single cell, ran the code again, but it also hid the colored cell.

    Strangely, i added a color background to a cell containing text, and it hid that one too.



    Sorry if i wasn't clear with my intention.. I'd like to hide rows containing blanks within a range, and leave anything that's visible. ie: text, numbers or background format color

    any ideas?



    thanks
    Jeff
    Reply With QuoteRegister To Reply
  8. 12-28-2009, 10:06 PM #8
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Hide blank Rows - non text/color

    Hello Jeff,

    Can you post the workbook? It will easier for me to find any problems using the original data and layout.
    Reply With QuoteRegister To Reply
  9. 12-29-2009, 01:03 AM #9
    jeff.p
    jeff.p is offline
    Registered User
    Join Date
    08-23-2007
    Posts
    58

    Re: Hide blank Rows - non text/color

    Sure,

    Here's a simple test worksheet..
    The objective is to show data as well as format color within a range.

    thanks

    Jeff
    Attached Files Attached Files
    Last edited by jeff.p; 12-29-2009 at 03:46 AM.
    Reply With QuoteRegister To Reply
  10. 12-29-2009, 01:59 AM #10
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Hide blank Rows - non text/color

    Hello Jeff,

    When I run the macro, the rows are hidden for the cells that are blank or colored. What were expecting to happen differently?

    To Run the Macro...
    To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.
    Last edited by Leith Ross; 12-29-2009 at 02:04 AM. Reason: Added instructions on running a macro
    Reply With QuoteRegister To Reply
  11. 12-29-2009, 03:53 AM #11
    jeff.p
    jeff.p is offline
    Registered User
    Join Date
    08-23-2007
    Posts
    58

    Re: Hide blank Rows - non text/color

    Sorry for the confusion


    The objective was to leave both data and colored cells as is, and only remove completely blank ones.
    Currently when i run the script, it removes the rows with blank cells and also removes the rows with colored format cells.

    Basically, i'd like to leave anything that's visible, and hide all the blanks.

    I updated my file attachment it this helps.

    thanks

    Jeff
    Reply With QuoteRegister To Reply
  12. 12-29-2009, 01:32 PM #12
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Hide blank Rows - non text/color

    Changing Leith's logic slightly,
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  13. 12-29-2009, 03:25 PM #13
    jeff.p
    jeff.p is offline
    Registered User
    Join Date
    08-23-2007
    Posts
    58

    Re: Hide blank Rows - non text/color

    Thanks, Shg

    with a slight modification:

    Please Login or Register  to view this content.
    Works great!
    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)

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