Conditional formatting to hide rows

Erica2261

New Member
Joined
Apr 17, 2008
Messages
31
Is there a way to hide rows in a spreadsheet based on the results in a given cell within each row? For example, if the result cell C6 is "inactive" can the entire row be hidden (and the same for all other rows in the range)?

I don't know how to code so I am trying to do this with a formula through conditional formatting. I am using Excel 2002.

Thanks!
Erica
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
You will need code for that.

What is the entire range and what causes a cell to contain "inactive" - do you enter that or does C6 contain a formula?
 

Erica2261

New Member
Joined
Apr 17, 2008
Messages
31
Thanks -

C6 contains the following formula:

=IF(OR(F7>$B$3,G7< $B$3),"inactive","active")

Range is A6:Z11

I am reasonably good with spreadsheets but never coded before so not sure code will help me!

Erica
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try this: right click the sheet tab, select View Code and paste in

Code:
Private Sub Worksheet_Calculate()
Dim i As Long
For i = 6 To 11
    Rows(i).Hidden = Range("C" & i).Value = "inactive"
Next i
End Sub
then press ALT + Q to close the code window. Press F9 to force a recalculation to see the effect.
 

Erica2261

New Member
Joined
Apr 17, 2008
Messages
31
VoG -

That was really cool and worked great. ..I have a follow-up question though. My spreadsheet is designed so that users can enter a date into cell $B$3 which drives whether the formula in C6 returns "active" or "inactive".

When I used your code with a date of 6/30/10 the inactive rows were hidden (which is great); however, when I changed the date to 8/6/10 (which flipped a cell from inactive to active) the row stayed hidden. Can I modify the code to unhide the row if the status changes?

Erica
 

Erica2261

New Member
Joined
Apr 17, 2008
Messages
31
VoG -

Please ignore prior posting. Not sure what I did, but it is working as intended now!

Thank you very much.
Erica
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,732
Messages
5,833,375
Members
430,206
Latest member
Sami Gaid

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
Top