New to VBA - Show/Hide rows based on target cell

[hoglund]

Hello, I'm new to this forum and have searched it over and over to try and find a solution to this. I've come close to getting the right code (I think..) but my lack of knowledge about VBA is leading to a simple mistake in my code I'm guessing..

I'm trying to have rows disappear on one worksheet based on a drop-down menu on another worksheet that has 12 months to select from. I will be replicating this code for multiple other worksheets that will act in a similar way.

Here's the code I've come up with;

Private Sub Worksheet_Change(ByVal Target As Range)
With Sheets("PI")
    Select Case Target.Address(False, False)
        Case "C4"
            .Rows("69:900").Rows.Hidden = Target.Value = "January"
            .Rows("5:68").Rows.Hidden = Target.Value = "February"
            .Rows("133:900").Rows.Hidden = Target.Value = "February"
            .Rows("5:132").Rows.Hidden = Target.Value = "March"
            .Rows("197:900").Rows.Hidden = Target.Value = "March"
            .Rows("5:196").Rows.Hidden = Target.Value = "April"
            .Rows("261:900").Rows.Hidden = Target.Value = "April"
            .Rows("5:260").Rows.Hidden = Target.Value = "May"
            .Rows("325:900").Rows.Hidden = Target.Value = "May"
            .Rows("5:324").Rows.Hidden = Target.Value = "June"
            .Rows("389:900").Rows.Hidden = Target.Value = "June"
            .Rows("5:388").Rows.Hidden = Target.Value = "July"
            .Rows("453:900").Rows.Hidden = Target.Value = "July"
            .Rows("5:452").Rows.Hidden = Target.Value = "August"
            .Rows("517:900").Rows.Hidden = Target.Value = "August"
            .Rows("5:516").Rows.Hidden = Target.Value = "September"
            .Rows("581:900").Rows.Hidden = Target.Value = "September"
            .Rows("5:580").Rows.Hidden = Target.Value = "October"
            .Rows("645:900").Rows.Hidden = Target.Value = "October"
            .Rows("5:644").Rows.Hidden = Target.Value = "November"
            .Rows("709:900").Rows.Hidden = Target.Value = "November"
            .Rows("5:708").Rows.Hidden = Target.Value = "December"
            .Rows("773:900").Rows.Hidden = Target.Value = "December"
    End Select
End With
End Sub

As you can see, I'm trying to get the rows to hide based on the value in C4. However, the only month that seems to be working is December (or whatever month is listed last). What am I missing to fix this?

I'm using Office 2007 on a laptop loaded with Windows XP SP3.

Thoughts?

 

[Burrgogi]

Why are you using VBA code for this? Just setup the worksheet using column headers and apply a filter. Have you considered this option?

 

[stnkynts]

This should get you started. I did through March. You are going to have to fill in the rest of it but you should get the idea. Note how I included the code to unhide all rows in the range that you are using prior to hiding. That way only the rows you want hidden are hidden.

Private Sub Worksheet_Change(ByVal Target As Range)
 
If Target.Address = "$C$4" Then
    Select Case Target.Value
        Case Is = "January"
            Rows("5:900").Hidden = False
            Rows("69:900").Hidden = True
        Case Is = "February"
            Rows("5:900").Hidden = False
            Rows("5:68").Hidden = True
            Rows("133:900").Hidden = True
        Case Is = "March"
            Rows("5:900").Hidden = False
            Rows("5:132").Hidden = True
            Rows("197:900").Hidden = True
        Case Is = "April"
        Case Is = "May"
        Case Is = "June"
        Case Is = "July"
        Case Is = "August"
        Case Is = "September"
        Case Is = "October"
        Case Is = "November"
        Case Is = "December"
    End Select
End If
End Sub

 

[hoglund]

Thanks for that stnkynts! That makes a lot more sense than what I was trying to do.

Burgoggi - I had tried to set it up using a filter at first, but due to the way the information is presented, it wasn't ideal. So I figured I'd try VBA. Thanks for the suggestion though!

 

[hoglund]

If I use the code above, it only seems to hide/show rows on the sheet where the dropdown menu is located.

If I wanted to have the dropdown menu (i.e C4) on one particular sheet ("DB"), and then have it hide/show all the rows on a different sheet ("PI", "FC", etc.) how could I get it to do that?

I've tried adding the With and End With for the particular worksheet that I want changed in multiple places in the code shown above - but with no luck. It only seems to change the rows that are shown/hidden on the sheet where the dropdown menu is located - is there an easy way to fix this?

I realize I could record macros so that everytime I change the cell on "DB" that it changes a related cell on "PI" or "FC" and achieve this. But I figured I'd ask to see if there is a cleaner way to do this.

Thanks.

 

[stnkynts]

Like this: Where the code is put in the same worksheet event as the worksheet with the dropdown list. You may need to change the sheet names ( in the parenthesis. Currently Sheet2) .

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim ws2 as Worksheet:      Set ws2 = Sheets("Sheet2")

If Target.Address = "$C$4" Then
    Select Case Target.Value
        Case Is = "January"
            ws2.Rows("5:900").Hidden = False
            ws2.Rows("69:900").Hidden = True
        Case Is = "February"
            ws2.Rows("5:900").Hidden = False
            ws2.Rows("5:68").Hidden = True
            ws2.Rows("133:900").Hidden = True
        Case Is = "March"
            ws2.Rows("5:900").Hidden = False
            ws2.Rows("5:132").Hidden = True
            ws2.Rows("197:900").Hidden = True
        Case Is = "April"
        Case Is = "May"
        Case Is = "June"
        Case Is = "July"
        Case Is = "August"
        Case Is = "September"
        Case Is = "October"
        Case Is = "November"
        Case Is = "December"
    End Select
End If
End Sub