# Hide / Unhide Rows based on what another row is doing

#### tlc53

##### Active Member
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.

### 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
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
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
Let's not worry about the hide aspect.
If just the unhide works, that would be great!

#### Michael M

##### Well-known Member

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

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
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
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
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]``````

Replies
5
Views
95
Replies
13
Views
194
Replies
9
Views
83
Replies
13
Views
104
Replies
10
Views
219

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