Hide Rows Based on single cell

[1upsman76]

Had to re-register as I don't have access to my old login, but hello to everyone here as I have been gone for a bit. I am rebuilding an old spreadsheet from scratch (other one is dead and gone).
After looking through about 30 or 40 of the posts and trying to get something to work I am at my wits end. About 3 years ago someone helped me with this and after not using it...I have forgotten how to accomplish this.

I have a cell AA2 on the sheet that displays the result from an input on another page. I am using that cell to decide which rows to hide on the same sheet. For Instance

If AA2 = "Test" then Rows 4 through 35 should be hidden.
If AA2 = "Test1" Then Rows 36 through 65 should be hidden.
If AA2 = "Test2" The Rows 65 through 100 should be hidden.

I've tried basic multiple variations of codes and so far nothing has worked.

I apprecaite any input and ideas.

Thanks!

 

[Peter_SSs]

1. What is this sheet name?

2. I assume AA2 contains a formula. What is that formula?

3. What is the other sheet name?

May be more questions, depending on the answers to these.

 

[Scott Huish]

Perhaps:

Sub test()
Rows.Hidden = False
Select Case UCase(Range("AA2"))
    Case "TEST"
        Rows("4:35").Hidden = True
    Case "TEST1"
        Rows("36:65").Hidden = True
    Case "TEST2"
        Rows("65:100").Hidden = True
End Select
End Sub

 

[1upsman76]

1. What is this sheet name? - "Results"

2. I assume AA2 contains a formula. What is that formula? =Calculator!F35

3. What is the other sheet name? "Calculator"

And no worries about more questions, the help is grealy appreciated

 

[1upsman76]

Tried stepping into it and didn't work. I had one similar to this, and have tried deleting the page and starting over as well. Thank you though!

Perhaps:

Sub test()
Rows.Hidden = False
Select Case UCase(Range("AA2"))
    Case "TEST"
        Rows("4:35").Hidden = True
    Case "TEST1"
        Rows("36:65").Hidden = True
    Case "TEST2"
        Rows("65:100").Hidden = True
End Select
End Sub

 

[Peter_SSs]

Then assuming cell F35 on 'Calculator' is changed manually, try this worksheet change code.

To implement ..

1. Right click the Calculator sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window.

4. Try changing F35 on Calculator.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Const</SPAN> TgtCel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "F35" <SPAN style="color:#007F00">'<- Cell to trigger code</SPAN><br>    <br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, Range(TgtCel)) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> Sheets("Results")<br>            .Rows("4:100").Hidden = <SPAN style="color:#00007F">False</SPAN><br>            <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Range(TgtCel).Value<br>                <SPAN style="color:#00007F">Case</SPAN> "Test"<br>                    .Rows("4:35").Hidden = <SPAN style="color:#00007F">True</SPAN><br>                <SPAN style="color:#00007F">Case</SPAN> "Test1"<br>                    .Rows("36:65").Hidden = <SPAN style="color:#00007F">True</SPAN><br>                <SPAN style="color:#00007F">Case</SPAN> "Test2"<br>                    .Rows("66:100").Hidden = <SPAN style="color:#00007F">True</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[1upsman76]

Thank you Peter, that worked for me.
I do appreciate it. The last time I made this spreadsheet the options and results were on all on the same sheet. In an effort to make it more readable I had to split it up.

Thank you again!