unhide rows based on value of particular cell?

[ajm]

how can i unhide a range of cells dependent upon the value of a particular cell above? so, if cell C16 is between 11 and 20, unhide rows 50-60, if between 21 and 30, unhide 50 - 70, etc etc

 

[SydneyGeek]

You need a bit of code to do this. The following assumes that:
1. You want to selectively show rows between 51 and 100
2. C16 is the trigger cell

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Range("C16")) Is Nothing Then Exit Sub
    Rows("51:100").RowHeight = 0
    Select Case Target.Value
        Case 11 To 20
            Rows("51:60").AutoFit
        Case 21 To 30
            Rows("61:70").AutoFit
        Case 31 To 40
            Rows("71:80").AutoFit
        Case 41 To 50
            Rows("81:90").AutoFit
        Case 51 To 60
            Rows("91:100").AutoFit
    End Select
End Sub

Note: I moved the opening cells down in groups of 10, for testing. Adjust to suit.

The code first hides all conditional rows, then displays the desired ones.

Denis

 

[ajm]

denis,

how can i loop it so if the number in the target cell changes, the hidden cells change accordingly?

should i just shorten each section to a piece of code itself? ie

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("C19")) Is Nothing Then Exit Sub
Rows("48:222").RowHeight = 0
Select Case Target.Value
Case 11 To 20
Rows("48:72").AutoFit
End Select
End Sub

Private Sub1 Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("C19")) Is Nothing Then Exit Sub
Rows("48:222").RowHeight = 0
Select Case Target.Value
Case 21 To 30
Rows("73:97").AutoFit
End Select
End Sub

etc etc etc???

ajm

 

[SydneyGeek]

The place where you hsow / hide, according to different values, is in the Select Case statement.

I created cutoffs 21 To 30, 31 To 40, 41 To 50 etc. Each of these is a Case statement.
The line directly below each of these shows which rows to unhide.
All you need to do to extend it is to add more Case statements.

If you want to vary WHICH rows appear, change Rows("61:70").Autofit (for example) to Rows("51:70").Autofit

The code goes into the module for the worksheet. Select the sheet tab, right-click, View Code, and paste the code in there. Every time you change C16, the code will run.

Denis

 

[ajm]

ok. made a mistake in not specifying that i needed to unhide more than JUST the particular segment. its cumulative so if the target cell is greater than 10, i want to see rows 1 - 20, if its greater than 20, i want to see rows 1 -30. as you suggest, just clean up therow selection.

many thanks Denis.

PS could this have been done with an If Else If...

 

[ajm]

Denis,

forgot to ask, if Target C19 is linked to another cell in the workbook or has a formula in it instead of just a number, how can the code that you gave me be modified to accommodate this?

 

[SydneyGeek]

Hi, to answer your last 2 posts:

1. Yes it could have been done iwth a nestsed If ... ElseIf but it gets messy. The Select Case statement is much easier to maintain if the number of conditions gets large, or will vary over time.

2. If C19 is linked to another cell (say, F19) then make the changing cell the target. The Worksheet_Change event doesn't fire on a cell that changes as the result of a link or calculation. This version of the code changes rows in Sheet2, based on a trigger value in Sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Sht As Worksheet
    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Range("C16")) Is Nothing Then Exit Sub
    Set Sht = Sheets("Sheet2")
    
    With Sht
        .Rows("51:100").RowHeight = 0
        Select Case Target.Value
            Case 11 To 20
                .Rows("51:60").AutoFit
            Case 21 To 30
                .Rows("61:70").AutoFit
            Case 31 To 40
                .Rows("71:80").AutoFit
            Case 41 To 50
                .Rows("81:90").AutoFit
            Case 51 To 60
                .Rows("91:100").AutoFit
        End Select
    End With
End Sub

Hope that helps

Denis

 

[ajm]

Denis,

this new code goes on to the sheet that has the Target range on? Am I correct? And then you Set Sht to the the sheet that contains the ranges that are to be hidden/unhidden?

ajm

 

[SydneyGeek]

Got in in one :wink:

Denis

 

[ajm]

Denis,

It just won't work for me. Can I be a real pain and PM or email you my working book? Its very small in size.

Andrew