VBA to Hide Rows Based on Cell Value.

[LauraEdson10]

I have a dropdown box in Cell A17 which is a list to give me the options of either "Yes" or "No".

If I select "No", rows 18:22 need to be hidden. If I select "Yes", rows 18:22 need to show.

Can anyone help me with the VBA formula please?

My sheet name is "Invoicing Instructions" if you needed to know.

Thank you.

 

[Ramballah]

I have a dropdown box in Cell A17 which is a list to give me the options of either "Yes" or "No".

If I select "No", rows 18:22 need to be hidden. If I select "Yes", rows 18:22 need to show.

Can anyone help me with the VBA formula please?

My sheet name is "Invoicing Instructions" if you needed to know.

Thank you.

Tbh there are many threads like these. even today there was one asking for the same but then with columns. might wanna look at that?
https://www.mrexcel.com/forum/excel...-unhide-columns-based-dropdown-selection.html
However in your case you dont need bigger or smaller than. its just a simple If range("A17") = "Yes" Then
Else
situation

[COLOR=#333333]            If Range("A17") = "Yes" Then .Range("18:22").Rows.Hidden = False Else .Range("18:22").Rows.Hidden = True[/COLOR]

 

[LauraEdson10]

I did see ones similar but theirs are based on a numerical cell value rather than text and mostly based upon multiple criteria so I couldn't figure out how to tailor the formula to suit

 

[Ramballah]

look reply #2 i editted it. that should work what you want. if you want i can add the sub and stuff in.

 

[Ramballah]

Sub Hidden()
Dim cel As Range
Dim rng1 As Range
Set rng1 = Range("18:22")
Set cel = Range("A17")
    If cel = "Yes" Then
        rng1.rows.Hidden = False
    Else
        rng1.rows.Hidden = True
    End If
End Sub

This works

 

[LauraEdson10]

It doesn't seem to be working, nothing happens?

 

[Ramballah]

It doesn't seem to be working, nothing happens?

It sure works for me. Are you sure you right click on your sheet and pressed view code and pasted it in? now u need a button to activate it. or press F5 if ur in the script.

 

[LauraEdson10]

It sure works for me. Are you sure you right click on your sheet and pressed view code and pasted it in? now u need a button to activate it. or press F5 if ur in the script.

Yeah, it's entered in exactly as you wrote. I press F5 and change from "Yes" to "No" and visavera, nothing happens

 

[LauraEdson10]

It's sort of working. If you press "No", then the box disappears. However, if you click "Yes" again, it just stays hidden?

Also, you have to press F5 within View Code everytime, it needs to be automatic.

 

[Ramballah]

Yeah, it's entered in exactly as you wrote. I press F5 and change from "Yes" to "No" and visavera, nothing happens

Hmm thats weird it works for me if i try it myself. Can you perhaps show me a screenshot of your worksheet?
If its "No" then the 4 rows will hide if its "Yes" they will just be there for everyone to watch.