Hide / Unhide Rows based on what another row is doing

[tlc53]

Hi there,

I've got an idea but I don't have the technical ability to write it nor do I know if it's possible.
Hiding rows based on a change of a formulated cell is not easy to do. In this particular example though, the data is manually entered up top and summarised down the bottom by formulas. I have this nifty code up top which adds a line one at a time, as needed by the user;


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Set Rng = Intersect(Target, [B21:B116])
If Not Rng Is Nothing Then Rng(2, 1).EntireRow.Hidden = False
End Sub

For the summary below which is completely formulated referring to the manual data entered above, is it possible to hide a row, if another row is hidden. For example, if row 21 is hidden, hide row 138. If row 22 hidden, hide row 139.

If hidden:.....Hide
Row 21........Row 138
Row 22........Row 139
Row 23........Row 140
Row 24........Row 141
All the way down to..
Row 116......Row 233

It would also need to work the other way round, so it shows when visible.

Thank you for your time!

 

[Michael M]

How does row 21,22,etc get hidden initially ??
maybe this though....

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Cells.Rows.EntireRow.Hidden = False
Set Rng = Intersect(Target, [B21:B116])
If Not Rng Is Nothing Then Rng(2, 1).EntireRow.Hidden = False
For r = 21 To 116
    If Rows(r).EntireRow.Hidden = True Then
        Rows(r + 117).EntireRow.Hidden = True
    End If
Next r
End Sub

 

[tlc53]

They start off being hidden. Ie. I manually hide them. I've also manually hidden 139:233.
I tried entering your code but now when I enter data in B21 it unhides rows 22:116, instead of just the next row, 22. The good news is, it does the same/unhides rows 139:233. So we're definitely heading in the right direction! I just need it to reveal one line at a time though..

 

[tlc53]

Let's not worry about the hide aspect.
If just the unhide works, that would be great!

 

[Michael M]

Ok, in that case I misunderstood your first post
you can change tru / false to suit your needs

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Cells.Rows.EntireRow.Hidden = False
Set Rng = Intersect(Target, [B21:B116])
If Not Rng Is Nothing Then Rng(2, 1).EntireRow.Hidden = False
    If Target.Rows.EntireRow.Hidden = True Then
        Target.Rows.EntireRow.Hidden = True
    End If
End Sub

 

[tlc53]

Hmm.. it's still not working right. It's still revealing all the lines at once, rather than just the one below.
I tried playing with the False/True but I'm a little in the dark to be honest.

 

[Michael M]

This then

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Set Rng = Intersect(Target, [B21:B116])
If Not Rng Is Nothing Then Rng(2, 0).EntireRow.Hidden = False
    If Target.Rows.EntireRow.Hidden = False Then
        Rows(Target + 1).EntireRow.Hidden = False
    End If
End Sub

 

[tlc53]

It's not liking that code, something to do with this line.. Rows(Target + 1).EntireRow.Hidden = False

I was playing around with it a little myself and the below works a little bit better. It reveals one line at a time at the top but reveals all lines down the bottom (instead of one at a time)..

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Set Rng = Intersect(Target, [B21:B116])
If Not Rng Is Nothing Then Rng(2, 1).EntireRow.Hidden = False
For r = 21 To 116
If Rows(r).EntireRow.Hidden = True Then
Rows(r + 116).EntireRow.Hidden = False
End If
Next r
End Sub

 

[Michael M]

You don't have any other code in the sheet module do you ??
The code works fine for me....but a slight mod

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Set Rng = Intersect(Target, [B21:B116])
If Not Rng Is Nothing Then Rng(2, 0).EntireRow.Hidden = False
    If Target.Rows.EntireRow.Hidden = False Then
        Rows(Target.Row + 1).EntireRow.Hidden = False
    End If
End Sub

 

[Akuini]

I might misunderstood what you want, but try this:

[FONT=lucida console][COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] Worksheet_Change([COLOR=Royalblue]ByVal[/COLOR] Target [COLOR=Royalblue]As[/COLOR] Range)
    [COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] Intersect(Target, Range([COLOR=brown]"B21:B116"[/COLOR])) [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]And[/COLOR] Target.Cells.Count = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]Then[/COLOR]
        Target.Offset([COLOR=crimson]117[/COLOR]).EntireRow.Hidden = Target.EntireRow.Hidden
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]
[/FONT]