VBA Unhide 5 rows at a time when cell selected

[tlc53]

Hi,

Column B is a description field where the user enters data. To reduce the visible size of the table, I want to hide rows until they are needed.
I would like, when they enter something in B20, 5 rows below are unhidden. Then when they get to B25 and enter data, another 5 rows are unhidden. Again at B30.
My current VBA code only unhides the one row below. Can someone please help amend the code, so it unhides 5 rows at a time?

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

Thanks so much!

 

[dermie_72]

I haven't tested this but I think if you change the If line to:

If Not rng Is Nothing Then selection.resize(5).EntireRow.Hidden = False

See if that helps.

 

[Peter_SSs]

I would try to make my code allow for the case when more than one cell at a time could be changed.
Also if B20 was empty and the user selected that cell and pressed the Delete key I assume that you would not want the next 5 rows unhidden since B20 is still empty.
So this would be my suggestion.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rng As Range, c As Range
  
  Set rng = Intersect(Target, Columns("B"))
  If Not rng Is Nothing Then
    For Each c In rng
      If Len(c.Value) > 0 And c.Offset(1).EntireRow.Hidden = True Then
        c.Offset(1).Resize(5).EntireRow.Hidden = False
        Exit For
      End If
    Next c
  End If
End Sub

 

[tlc53]

If Not rng Is Nothing Then selection.resize(5).EntireRow.Hidden = False

Thank you. It's not quite working. Usually the code would run if text was inputted and then you push enter/leave the cell.
However, with this code, it only runs once text is entered and then you go back and delete the text, then it runs

 

[tlc53]

I would try to make my code allow for the case when more than one cell at a time could be changed.
Also if B20 was empty and the user selected that cell and pressed the Delete key I assume that you would not want the next 5 rows unhidden since B20 is still empty.
So this would be my suggestion.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rng As Range, c As Range
 
  Set rng = Intersect(Target, Columns("B"))
  If Not rng Is Nothing Then
    For Each c In rng
      If Len(c.Value) > 0 And c.Offset(1).EntireRow.Hidden = True Then
        c.Offset(1).Resize(5).EntireRow.Hidden = False
        Exit For
      End If
    Next c
  End If
End Sub

Love this code! Works perfectly and is super fast!
Really appreciated. Thank you

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.