Excel: Hide Multiple Row Selections Based on Other Cell Value

Posted on 2014-08-13
Last Modified: 2014-08-14

Attached is a spreadsheet with 2 sheets. On the trigger sheet, when the value of a yellow cell is yes, I would like the corresponding coloured rows on the 'rows to hide' sheet to become hidden. I would also like the image set atop the rows to be hidden.

Also for the opposite, if the value is no longer Yes, the rows are to be unhidden and the image is to be visible.

Is it possible to easily embed images within cells so they can move up/down and be hidden with cells?

Thanks for your help.
Question by:dabug80
    LVL 27

    Expert Comment

    by:Glenn Ray
    To answer your last question first, yes, you can change the image attributes so that they move/resize with their associated cells like so:
    object positioning - move and resize with cellsThat will allow the graphics to be hidden/shown along with the rows.

    Regarding your main question:  do you want to show/hide the specific rows in this example or ANY colored rows on that sheet?
    So either:
    Red = rows 6:11
    Yellow =  rows 14:20
    Green = rows 23:29

    Any Red, Yellow, or Green rows.

    LVL 27

    Accepted Solution

    IF, your answer to my last question is that the specific, numbered rows are to be hidden/unhidden, this Worksheet_Activate event inserted in the "Rows to Hide" sheet object module will make the rows follow your requested behavior:
    Private Sub Worksheet_Activate()
        If Sheets("Trigger").Range("B5").Value = "Yes" Then
            Rows("6:11").EntireRow.Hidden = True
            Rows("6:11").EntireRow.Hidden = False
        End If
        If Sheets("Trigger").Range("B8").Value = "Yes" Then
            Rows("14:20").EntireRow.Hidden = True
            Rows("14:20").EntireRow.Hidden = False
        End If
        If Sheets("Trigger").Range("B11").Value = "Yes" Then
            Rows("23:29").EntireRow.Hidden = True
            Rows("23:29").EntireRow.Hidden = False
        End If
    End Sub

    Open in new window

    I've attached a modified version of your workbook for you to test.

    LVL 1

    Author Closing Comment

    Excellent. Works well, thanks
    LVL 27

    Expert Comment

    by:Glenn Ray
    You're welcome.  


    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now