VBA Code - Unhide block of rows

[tlc53]

Hi,

Currently I have a code which unhides the row below if cell J45 is populated.
However, I would like it to unhide a block of rows. So instead of just unhiding 1 row below, it unhides 21 rows below (rows 46:66). Is there anyway to amend this code to do that?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("J45:J45")) Is Nothing And Target.Cells.Count = 1 Then
        Target.Offset(1).EntireRow.Hidden = Target.EntireRow.Hidden
    End If
End Sub

Thanks.

 

[footoo]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$J$45" Then
    Target(2).Resize(21).EntireRow.Hidden = False
End If
End Sub

 

[MoshiM]

Hi,

Currently I have a code which unhides the row below if cell J45 is populated.
However, I would like it to unhide a block of rows. So instead of just unhiding 1 row below, it unhides 21 rows below (rows 46:66). Is there anyway to amend this code to do that?

Thanks.

Target.Offset(1).resize(21).EntireRow.Hidden = False

Edit looks like I was late in submitting my post

 

[My Aswer Is This]

Edit

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  7/22/2019  10:50:50 PM  EDT
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = Range("J45").Address Then Rows(Target.Row).Offset(1).Resize(21).Hidden = False
End Sub

 

[My Aswer Is This]

If you want to Toggle those rows from Hidden to Visible.
Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  7/22/2019  11:31:57 PM  EDT
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = Range("J45").Address Then Target(2).Resize(21).EntireRow.Hidden = Not Target(2).Resize(21).EntireRow.Hidden
End Sub

 

[tlc53]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$J$45" Then
    Target(2).Resize(21).EntireRow.Hidden = False
End If
End Sub

Thank you! This did the trick