Results 1 to 6 of 6

Hide row based on conditional formatting

This is a discussion on Hide row based on conditional formatting within the Excel Questions forums, part of the Question Forums category; We have a spreadsheet that uses lookups for many fields including column F which will return either blank or "1". ...

  1. #1
    New Member
    Join Date
    Nov 2010
    Posts
    2

    Default Hide row based on conditional formatting

    We have a spreadsheet that uses lookups for many fields including column F which will return either blank or "1".

    We would like to hide any row in which the value of Column F=1

    I have found places this can be done in Visual Basic, but as neither myself or the other person who will likely make edits to this workbook are familiar with VB and less likely to make edits to it later, I was asked to see if there is a way to do so with conditional formatting.

    If not, what is the most straightforward way to do so in VB?

    Data rows start on row 4 (as this will be a continuously growing file, to end of spreadsheet)

    Tahnks in advance for any assistance!

  2. #2
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    11,581

    Default Re: Hide row based on conditional formatting

    Consider using the Autofilter feature.

    Excel Filters: Excel AutoFilter Basics

  3. #3
    New Member
    Join Date
    Nov 2010
    Posts
    2

    Default Re: Hide row based on conditional formatting

    That was my first instinct also, but unfortunately they want it to be seamless, just do it in the background so others who don't know how to filter go look at it, they don't have to remember how to filter. Seems, crazy, I know.

  4. #4
    MrExcel MVP HalfAce's Avatar
    Join Date
    Apr 2003
    Location
    Alaska
    Posts
    9,232

    Default Re: Hide row based on conditional formatting

    Hi, and welcome to the board.
    As far as I know, we can't hide / unhide rows using conditional formatting.
    About the closest thing to that I can think of would be to CF the entire row to have the font be the same color as the background color.
    (Wouldn't hide the row(s) but would (basically) hide the data in them.)

    If a bit of VBA would be acceptable - AND if I'm correct in thinking that the 0 or 1 values in column F are the results of formulas, then perhaps this might help.
    (Right click the sheet's name tab, choose View code and paste something like this into the white area on the right that is the sheet's code module.)
    Code:
    Private Sub Worksheet_Calculate()
    Dim LstRw As Long, Rw As Long
    Application.ScreenUpdating = False
    
    '''Define LstRw as the last row in column F with data.
    LstRw = Cells(Rows.Count, "F").End(xlUp).Row
    
    '''Un-hide all rows to start with
    Range("F4:F" & LstRw).EntireRow.Hidden = False
    
    ''' Go through column F, (starting at row 4) _
        & hide all rows with a value of 1
    For Rw = 4 To LstRw
      If Cells(Rw, "F") = 1 Then Cells(Rw, "F").EntireRow.Hidden = True
    Next Rw
    
    Application.ScreenUpdating = True
    End Sub
    Next, press ALT+Q to close the VB editor and get back to your sheet to try it out.

    Now this code will execute every time the worksheet calculates.


    Hope it helps.
    Last edited by HalfAce; Nov 26th, 2010 at 03:33 PM.
    Does anyone else find it kinda cruel that they spelled the word lisp with an s?


  5. #5
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    11,581

    Default Re: Hide row based on conditional formatting

    I agree with HalfAce. I'm just slower at typing.

    Here is the event macro I came up with...
    Code:
    Private Sub Worksheet_Calculate()
    
        Application.EnableEvents = False
        Range("F4:F" & Rows.Count).AutoFilter Field:=1, Criteria1:="<>1", Visibledropdown:=False
        Application.EnableEvents = True
        
    End Sub

  6. #6
    MrExcel MVP HalfAce's Avatar
    Join Date
    Apr 2003
    Location
    Alaska
    Posts
    9,232

    Default Re: Hide row based on conditional formatting

    I also like the use of AutoFilter for this, I'm just a bit reluctant to suggest it a lot of times here because there can be some little things specific to a worksheet or data range that can have an effect so I generally like to actually see the data I'm working with before I use AutoFilter so I can be sure I didn't screw something up (or overlook it).

    But... if it were my workbook, (or I got to look at this one), I would most likely go with AutoFilter like AlphaFrog did just so it didn't have to use a loop.
    Does anyone else find it kinda cruel that they spelled the word lisp with an s?


Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com