+ Reply to Thread
Results 1 to 8 of 8

Need help to show/hide rows based on values from a cell

  1. #1
    Registered User
    Join Date
    12-14-2012
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    5

    Need help to show/hide rows based on values from a cell

    Hello! I was wondering if anyone might be able to help me? I'm trying to find a way to accomplish the following:

    If cell A7 is null, hide rows 11-32
    If cell A7 = no, hide rows 11-32
    If cell A7 = yes, show rows 11-32

    Would anyone be able to assist?

    Thank you!

  2. #2
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,193

    Re: Need help to show/hide rows based on values from a cell

    Hi, concurmgr,

    you need to tell us how the entry in that cell is made: directly from a user or via Formumla?

    For a direct entry you could use a static macro like this
    Please Login or Register  to view this content.
    Evaluation is made on Range("A7"), and only if the entry is yes the rows will be shown.

    You could use an event to do so (code goes behind the sheet):
    Please Login or Register  to view this content.
    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  3. #3
    Registered User
    Join Date
    12-14-2012
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Need help to show/hide rows based on values from a cell

    Hello Holger! Thank you so much for your reply! I apologize for not indicating how A7 would be populated.

    A7 is driven by a validation list. So the user will manually select one of three values in that cell based on the list. Does that impact the suggested code above? Thank you!

  4. #4
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,193

    Re: Need help to show/hide rows based on values from a cell

    Hi, concurmgr,

    Data/Validation will trigger the Worksheet_Change-Event so the provided code should work fine with that.

    Ciao,
    Holger

  5. #5
    Registered User
    Join Date
    12-14-2012
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Need help to show/hide rows based on values from a cell

    Thank you Holger! For some reason, the code isn't working. I copied and pasted it directly, but when I select any value in A7, it exposes all rows. So sorry to ask, but do you have any suggestions. Perhaps I am missing something by doing a direct copy/paste of the code above?

  6. #6
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,193

    Re: Need help to show/hide rows based on values from a cell

    Hi, concurmgr,

    only suggestion right now would be that the code is not where itīs got to be placed: behind the sheet where the event is triggered. Maybe you can have a look at the sample attached.

    Ciao,
    Holger
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    12-14-2012
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Need help to show/hide rows based on values from a cell

    This is great! I see that the code works, only in reverse. (It is hiding the rows when the value is "yes".) Is there a way to reverse that? It should only expose the rows if the value is "yes", but hide them if any other value.

  8. #8
    Registered User
    Join Date
    12-14-2012
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Need help to show/hide rows based on values from a cell

    Nevermind! I got it! Thank you so much for your help!!

    Option Explicit

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("A7")) Is Nothing Then
    Rows("11:32").Hidden = LCase(Target) <> "yes"
    End If
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1