Hide row based on conditional formatting

[lisalewis12]

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!

 

[AlphaFrog]

Consider using the Autofilter feature.

Excel Filters: Excel AutoFilter Basics

 

[lisalewis12]

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.

 

[HalfAce]

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.)

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.

 

[AlphaFrog]

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

Here is the event macro I came up with...

Private Sub Worksheet_Calculate()

    Application.EnableEvents = False
    Range("F4:F" & Rows.Count).AutoFilter Field:=1, Criteria1:="<>1", Visibledropdown:=False
    Application.EnableEvents = True
    
End Sub

 

[HalfAce]

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.