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
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,516
Messages
5,511,754
Members
408,864
Latest member
cmajewsk

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top