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.

 

[dmt32]

try this

Place code in your worksheets code page (right click tab > View Code)

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address = "$A$7" Then Me.Rows("18:22").Hidden = UCase(Target.Value) = "NO"
End Sub

Dave

 

[LauraEdson10]

https://www.dropbox.com/sh/lgycmrioinnpiwr/AADvbewKw55_5M6uYUQ3yMvUa?dl=0

Photos linked

 

[LauraEdson10]

try this

Place code in your worksheets code page (right click tab > View Code)

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address = "$A$7" Then Me.Rows("18:22").Hidden = UCase(Target.Value) = "NO"
End Sub

Dave

That's doing the exact same unfortunately, it doesn't appear if already hidden

 

[Ramballah]

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.

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

This is an automatic version

 

[Fluff]

It looks like you have merged cells, which are an abomination & should be avoided like the plague.
That said try changing the range to B17 not A17

 

[Ramballah]

It looks like you have merged cells, which are an abomination & should be avoided like the plague.
That said try changing the range to B17 not A17

I just merged A17 and B17 like he did and my code still works for me. Not sure why it's not for him.

 

[LauraEdson10]

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

This is an automatic version

When I press F5, it says "Compile Error: Expected: end of statement" - the top line of the code is in red??

 

[Ramballah]

When I press F5, it says "Compile Error: Expected: end of statement" - the top line of the code is in red??

Oh yeah uhm its because the Dim is pasted behind it. just press enter between As Range) and Dim cel As Range. this is just mrexcel.com's fault

 

[LauraEdson10]

Oh yeah uhm its because the Dim is pasted behind it. just press enter between As Range) and Dim cel As Range. this is just mrexcel.com's fault

Thanks, that's working better. So it was set as "Yes" when I entered the formula. I changed it to "No" and the rows disappeared (just as I wanted). However, I press "Yes" again and they just stay hidden still?

I will post a video if you click on the Dropbox link again if a few minutes.

 

[Ramballah]

Thanks, that's working better. So it was set as "Yes" when I entered the formula. I changed it to "No" and the rows disappeared (just as I wanted). However, I press "Yes" again and they just stay hidden still?

I will post a video if you click on the Dropbox link again if a few minutes.

Okay i'll watch it. That's so weird because it works for me