+ Reply to Thread
Results 1 to 10 of 10

Hide select rows based on value in a dynamic range

  1. #1
    Registered User
    Join Date
    03-28-2012
    Location
    Location
    MS-Off Ver
    Excel 2010
    Posts
    12

    Cool Hide select rows based on value in a dynamic range

    Hi,

    I have a worksheet that has a number of header rows that should always be visible and can't be changed (Rows 1-10). This worksheet also has 3 rows at the end of the worksheet that contains summation formulas which should always be visible and can't be changed.

    In between these two sections, the body of the worksheet has a number of dynamic rows that can increase or decrease as users add or delete rows.

    Column C contains a drop-down list of three options. I want to have buttons that, when clicked, will hide all rows where the value in column C equals to the other two options as well as hide any blank or unused rows up to the bottom Totals line. i.e. If I click the button that says "A", only the lines from Row 11 to the bottom line that contain "A" and "All" will be shown. The Totals line will always be visible.

    I've attached a sample of the workbook... hopefully it gives a better idea of what I'm trying to accomplish.

    SampleBk.xlsx

    Any help would be greatly appreciated!

    Thanks

  2. #2
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Hide select rows based on value in a dynamic range

    Try Attachment 259611
    Thanks,
    Solus


    Please remember the following:

    1. Use [code] code tags [/code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    Highlight the code in your post and press the # button in the toolbar.
    2. Show appreciation to those who have helped you by clicking below their posts.
    3. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

    "Slow is smooth, smooth is fast."

  3. #3
    Registered User
    Join Date
    03-28-2012
    Location
    Location
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Hide select rows based on value in a dynamic range

    Thanks for the quick response!

    The code hide the required cells, but:

    1. It also hid the rows that were labelled "All"... these ones should always show
    2. Clicking on the options hides rows correctly only if all are showing. i.e. if I click "Iron Man", then click "Hulk", all rows are hidden. Can it be done such that clicking "Iron Man" will only display "Iron Man" and "All" rows and then clicking "Hulk" would only display "Hulk" and "All" rows?

    Thank you!

  4. #4
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Hide select rows based on value in a dynamic range

    Try this: Change myValue to match what you need for each button

    Please Login or Register  to view this content.

  5. #5
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Hide select rows based on value in a dynamic range

    My mistake. Amended. Attachment 259620

  6. #6
    Registered User
    Join Date
    03-28-2012
    Location
    Location
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Hide select rows based on value in a dynamic range

    I've tested out the second book that you attached (Amended one).

    Perfect, I'm seeing the "All" lines now! However, if I click the "Thor" option, there are two "All" lines that show in the bottom... can it only display the "All" line where there is a Thor in that part?

    The second issue is still present: "2. Clicking on the options hides rows correctly only if all are showing. i.e. if I click "Iron Man", then click "Hulk", all rows are hidden. Can it be done such that clicking "Iron Man" will only display "Iron Man" and "All" rows and then clicking "Hulk" would only display "Hulk" and "All" rows?"

    Thanks in advance! ... really appreciate your help!

  7. #7
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Hide select rows based on value in a dynamic range

    That was a fun problem... and took a little more effort. Lets try:

  8. #8
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Hide select rows based on value in a dynamic range

    Sorry. Missed the second part of your problem. Final attachment Attachment 259644

  9. #9
    Registered User
    Join Date
    03-28-2012
    Location
    Location
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Hide select rows based on value in a dynamic range

    Wow, that is awesome! This is exactly it... thank you so much for your help!

  10. #10
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Hide select rows based on value in a dynamic range

    Glad to help. If you are satisfied with the answer, please mark the thread as [SOLVED] using the thread tools at the top.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How to hide rows based on cell value, row range is dynamic.
    By csh8428 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-03-2012, 02:05 PM
  2. Hide rows in range 2 based on filter selection in range 1
    By gtol in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 02-28-2012, 02:09 PM
  3. Hide numeric rows of dynamic range
    By Code Flunkie in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-31-2011, 09:53 AM
  4. Select dynamic range based on non-empty cells
    By oOarthurOo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-07-2009, 01:42 PM
  5. Hide Rows in Range based on criteria
    By Hot Soup in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-07-2009, 10:16 AM

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