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.

 

[LauraEdson10]

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

Just uploaded. Hopefully you can see what I mean now.. it's so strange.

 

[Ramballah]

Just uploaded. Hopefully you can see what I mean now.. it's so strange.

Yes, I have watched the video and im not sure what is going on. Because for me it works completely fine. Have you tried saving the workbook and re-opening it? Or do you have a code going on for row 18:22 ? I'm not sure what's causing this. What if you manually type Yes and No? (I tried using data validation like you but still worked)

 

[LauraEdson10]

Yes, I have watched the video and im not sure what is going on. Because for me it works completely fine. Have you tried saving the workbook and re-opening it? Or do you have a code going on for row 18:22 ? I'm not sure what's causing this. What if you manually type Yes and No? (I tried using data validation like you but still worked)

I took data validation off and manually typed it but it didn't make any difference. I shut down the workbook and re-opened it and that hasn't helped either. I have various other VBA codes in the workbook but it's a completely new sheet I've created today so can't be anything else linked to that row.

 

[Ramballah]

I took data validation off and manually typed it but it didn't make any difference. I shut down the workbook and re-opened it and that hasn't helped either. I have various other VBA codes in the workbook but it's a completely new sheet I've created today so can't be anything else linked to that row.

What excel are you using?
I'm really clueless at this point i'd suggest making a new thread asking for help with this new problem that if its going back to yes it wont change back.

 

[LauraEdson10]

What excel are you using?
I'm really clueless at this point i'd suggest making a new thread asking for help with this new problem that if its going back to yes it wont change back.

I have the office 365 subscription to it's whatever the latest version is. Yeah possibly so, it just doesn't make sense.

I just make a whole new workbook and tried to create the same set up. When I typed "Yes", it hid all the rows??? I'm getting even more confused now.

 

[LauraEdson10]

What excel are you using?
I'm really clueless at this point i'd suggest making a new thread asking for help with this new problem that if its going back to yes it wont change back.

Hang on a second!! I pressed the green "Play" symbol within the View Code screen rather than pressing F5 and it actually seems to be working correctly!!!

Thank you so much!

 

[Ramballah]

Hang on a second!! I pressed the green "Play" symbol within the View Code screen rather than pressing F5 and it actually seems to be working correctly!!!

Thank you so much!

Wooahh what???? thats so weird.
Anyway use this code. Because this only runs the macro when A17 changes. its the same code but its a bit faster if you edit other cells it wont run the code. my bad for not doing that

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A17")) Is Nothing Then
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 If
End Sub

 

[Ramballah]

Wooahh what???? thats so weird.
Anyway use this code. Because this only runs the macro when A17 changes. its the same code but its a bit faster if you edit other cells it wont run the code. my bad for not doing that

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A17")) Is Nothing Then
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 If
End Sub

Oops, im not sure if you have the A1:B100 version. but i pressed edit post and made it A17 not sure if you got A1:B100 or A17 now. but it should be A17

 

[steve the fish]

Target is a given variable in a worksheet change event so you can use it like this for example:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng as Range

Set Rng = Intersect(Target, Range("A17"))
If Not Rng Is Nothing Then
    Select Case UCase(Rng.Value)
        Case "NO"
            Rows("18:22").EntireRow.Hidden = True
        Case "YES"
            Rows("18:22").EntireRow.Hidden = False
    End Select
End If
End Sub