Hide Unhide Rows
Results 1 to 10 of 10

Thread: Hide Unhide Rows
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Hide Unhide Rows

    Helloe everyone,

    I am completely new to VBA so your help will be appreciated.

    I want automatically hide/unhide rows based on cell value. The cell value could have only 2 options: YES or NO

    So whenever cell value= YES then hide rows 2:10 and unhide 10:15
    whenever cell value= NO then unhide rows 2:10 and hide 10:15

    Thanks for your help!

  2. #2
    Board Regular
    Join Date
    Dec 2018
    Location
    Poland
    Posts
    203
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Hide Unhide Rows

    Hi,
    I need you provide more information. What do you mean by whenever? What is the range the algorism should chech the yes/no value?

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide Unhide Rows

    Thanks for the reply.

    Basically I am making a form. User is asked 'Do you have a second citizneship?" If he types in Yes in the cell then multiple rows appear asking him to fill in country, passport number etc.. When the user types in No or leaves it blank then no further questions (rows) appear.

    Was I able to explain it?

  4. #4
    Board Regular
    Join Date
    Dec 2018
    Location
    Poland
    Posts
    203
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Hide Unhide Rows

    User tyoes yes or no in one cell is that right?

  5. #5
    New Member
    Join Date
    Aug 2019
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide Unhide Rows

    So whenever user types YES then hide rows 2:10 and unhide 10:15
    So whenever user types NO then unhide rows 2:10 and hide 10:15

  6. #6
    Board Regular
    Join Date
    Dec 2018
    Location
    Poland
    Posts
    203
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Hide Unhide Rows

    You do not get me I suppose.
    In order to hide/unhide rows 2:10,10:15 I need to know which cell or range of cells I need to check if user types in yes or no value but you have not provided me with such information yet.

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,857
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Hide Unhide Rows

    How about
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
       If Target.CountLarge > 1 Then Exit Sub
       If Target.Address(0, 0) = "A1" Then
          Rows("2:10").Hidden = LCase(Target) = "yes"
          Rows("10:15").Hidden = LCase(Target) <> "yes"
       End If
    End Sub
    Change the value in red to the cell with the Yes/No answer.
    This needs to go in the relevant sheet module
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    New Member
    Join Date
    Aug 2019
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide Unhide Rows

    Hi,

    The curren active sheet where the rows are to be hidden is Sheet1
    The value of YES or NO is types in Cell A2 in different sheet calles Sheet 2
    Is this what you needed?

    Thankyou very much

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,857
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Hide Unhide Rows

    In that case try
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
       If Target.CountLarge > 1 Then Exit Sub
       If Target.Address(0, 0) = "A2" Then
          Sheets("Sheet1").Rows("2:10").Hidden = LCase(Target) = "yes"
          Sheets("Sheet1").Rows("10:15").Hidden = LCase(Target) <> "yes"
       End If
    End Sub
    This needs to go in the sheet module for sheet2
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    Board Regular
    Join Date
    Dec 2018
    Location
    Poland
    Posts
    203
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Hide Unhide Rows

    Hi,
    Exactly, this is what I was asking about. Here you have the code. Paste it worksheets 2 (not in the module).
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws as worksheet
    Set ws = worksheets(2)
    If Not Intersect(Target, Range("A2")) Is Nothing Then
       ws.Rows("2:10").Hidden = LCase(Target) = "yes"  
     ws.Rows("10:15").Hidden = LCase(Target) <> "yes"
    
    End if
    Set ws= nothing
    End sub

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
  •