Use drop down menu to hide/unhide rows

[JCK11]

Hi All

I am very new to coding in excel and have properly a easy questions that I hope one of you could help me with:

I have created a drop down menu with option "Yes" and "No", the drop down menu is located in a cell I have called "hidemonths"

The months I like to have the option to hide are all in rows. The row numbers are:
15, 17:27, 41:51, 53:63, 65:75, 77:87, 89:99, 101:111, 113:123, 125:135, 137:147, 149:159, 161:171, 173:183, 185:195, 197:207, 209:219, 221:231, 233:243, 245:255, 257:267


I have tried to with a very simpel code to set this up. First for just Row 15 tough it of course should be for all the rows. This without luck... I have the code you see below, and hope one of you could help me get it right...

If [hidemonths] = “Yes” Then
Rows(15).Visible = False
Else
Rows(15).Visible = True
End If


End Sub


Thanks in advance for the help... Hope the issue is clear...

 

[Fluff]

Hi & welcome to MrExcel
If your drop down is data validation. Try this

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address <> "[COLOR=#ff0000]$J$3[/COLOR]" Then Exit Sub
    If Target.Value = "Yes" Then
        Range("15:15, 17:27, 41:51, 53:63, 65:75, 77:87, 89:99, 101:111, 113:123, 125:135, 137:147, 149:159, 161:171, 173:183, 185:195, 197:207, 209:219, 221:231, 233:243, 245:255, 257:267").EntireRow.Hidden = True
    Else
        Range("15:15, 17:27, 41:51, 53:63, 65:75, 77:87, 89:99, 101:111, 113:123, 125:135, 137:147, 149:159, 161:171, 173:183, 185:195, 197:207, 209:219, 221:231, 233:243, 245:255, 257:267").EntireRow.Hidden = False
    End If

End Sub

Change the part in red to the match the cell address with your drop down.
This needs to go in the relevant sheet module.
Right click on the sheet tab > View Code. & then paste the above into the window that opens up.

 

[JCK11]

Hi Fluff and others
Thanks for the quick reply.

I am indeed trying to do this with validation.


Anyhow when I copy your code into my code view and change the cell name to the cell with my drop down menu nothing really happens when I chose yes and no afterwards?
As mentioned I am still new in this, so might be I am overseeing something. I tried to change make a new dropdown menu and refer to that one but same result...

Any suggestions how to find the issue?

 

[Fluff]

What cell is the drop down in?

 

[JCK11]

Hi again

The drop down is in a cell I renamed to: "hidemonths" so that no matter if I wanted the copy the cell somewhere else the code should work.

Anyhow i thought this was the issue to I made a new drop down in cell g6. But this did not work.

To answer your question short. The drop down is now in cell g6.

 

[Fluff]

Remove the code you've already got & try this

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Range("hidemonths"), Target) Is Nothing Then Exit Sub
    If LCase(Target.Value) = "yes" Then
        Range("15:15, 17:27, 41:51, 53:63, 65:75, 77:87, 89:99, 101:111, 113:123, 125:135, 137:147, 149:159, 161:171, 173:183, 185:195, 197:207, 209:219, 221:231, 233:243, 245:255, 257:267").EntireRow.Hidden = True
    Else
        Range("15:15, 17:27, 41:51, 53:63, 65:75, 77:87, 89:99, 101:111, 113:123, 125:135, 137:147, 149:159, 161:171, 173:183, 185:195, 197:207, 209:219, 221:231, 233:243, 245:255, 257:267").EntireRow.Hidden = False
    End If

End Sub

 

[JCK11]

Thanks a lot this works very well...

Your help is appreciated!

 

[Fluff]

Glad to help & thanks for the feedback