Hide row based on conditional formatting

lisalewis12

New Member
Joined
Nov 26, 2010
Messages
2
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!
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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.
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top