VBA Code - Unhide block of rows
Results 1 to 7 of 7

Thread: VBA Code - Unhide block of rows

  1. #1
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    260
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Code - Unhide block of rows

    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?



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

  2. #2
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,517
    Post Thanks / Like
    Mentioned
    34 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA Code - Unhide block of rows

    Code:
    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
    Last edited by footoo; Jul 22nd, 2019 at 10:39 PM.

  3. #3
    Board Regular
    Join Date
    Jan 2018
    Posts
    186
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code - Unhide block of rows

    Quote Originally Posted by tlc53 View Post
    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.
    Code:
    Target.Offset(1).resize(21).EntireRow.Hidden = False
    Edit looks like I was late in submitting my post
    Last edited by MoshiM; Jul 22nd, 2019 at 10:42 PM.

  4. #4
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,683
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: VBA Code - Unhide block of rows

    Edit
    Last edited by My Aswer Is This; Jul 22nd, 2019 at 10:46 PM.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  5. #5
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,683
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: VBA Code - Unhide block of rows

    Try this:
    Code:
    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
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  6. #6
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,683
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: VBA Code - Unhide block of rows

    If you want to Toggle those rows from Hidden to Visible.
    Try this:
    Code:
    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
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  7. #7
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    260
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code - Unhide block of rows

    Quote Originally Posted by footoo View Post
    Code:
    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •