Hide / Unhide Rows based on what another row is doing

tlc53

Active Member
Joined
Jul 26, 2018
Messages
365
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!
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,267
Office Version
  1. 2013
Platform
  1. Windows
How does row 21,22,etc get hidden initially ??
maybe this though....

Code:
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

Active Member
Joined
Jul 26, 2018
Messages
365
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

Active Member
Joined
Jul 26, 2018
Messages
365
Let's not worry about the hide aspect.
If just the unhide works, that would be great!
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,267
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

Code:
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
 
Last edited:

tlc53

Active Member
Joined
Jul 26, 2018
Messages
365
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

Well-known Member
Joined
Oct 27, 2005
Messages
19,267
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

This then
Code:
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

Active Member
Joined
Jul 26, 2018
Messages
365
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

Well-known Member
Joined
Oct 27, 2005
Messages
19,267
Office Version
  1. 2013
Platform
  1. Windows
You don't have any other code in the sheet module do you ??
The code works fine for me....but a slight mod

Code:
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

Well-known Member
Joined
Feb 1, 2016
Messages
2,575
Office Version
  1. 365
Platform
  1. Windows
I might misunderstood what you want, but try this:

Code:
[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]
 

Watch MrExcel Video

Forum statistics

Threads
1,109,163
Messages
5,527,163
Members
409,750
Latest member
BorisYeltsin

This Week's Hot Topics

Top