Auto Hide & Unhide Rows in Excel

Anonymous
2019-10-30T10:31:02+00:00

Hi, All.

I have to create an excel spreadsheet where the next 2 rows stay hidden if the answer is No or N/A or blank.

In the sample below:

* Column A with item number (1,2,3....) has a drop down list of ("Yes"or "No"or "N/A") in column C. I used Data Validation for this.

* Rows with no item number should stay hidden (rows 10,11,13,14,16&17) until "Yes" has been selected from the drop down list in column C.

* If the item in column C (C9) has no answer (or blank), rows 10 & 11 should stay hidden - I hide the rows manually for this purpose.

* Items with "No"or "N/A" has been selected from the drop down list in column C, the next 2 rows should stay hidden. Rows 16 & 17 should stay hidden in this sample.

* If "Yes" has been selected from the drop down list in column C, the content in column B for the next 2 rows (rows 13&14) will appear. 

* Column B has IF formula that auto populates if the answer is "Yes".

I will really appreciate your assistance on this.

Thank you in advance!

Regards,

Shaynne

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

9 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  2. Anonymous
    2019-10-30T12:32:56+00:00

    Thank you, again Hans.

    Sorry for asking too many question but how do I resize the rows to fit the contents in column B.

    Regards,

    Shaynne

    Was this answer helpful?

    0 comments
  3. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  4. Anonymous
    2019-10-30T11:40:28+00:00

    Hi, Hans.

    Thank you so much for the prompt response.

    When I tried to run the code for the first it worked but when I tried to protect the sheet, the next 2 rows didn't appear when I selected yes.

    Kind Regards,

    Shaynne

    Was this answer helpful?

    0 comments
  5. HansV 462.7K Reputation points MVP Volunteer Moderator
    2019-10-30T11:07:01+00:00

    Right-click the sheet tab.

    Select 'View Code' from the context menu.

    Copy the following code into the worksheet module:

    Private Sub Worksheet_Change(ByVal Target As Range)

        Dim rng As Range

        If Not Intersect(Range("C:C"), Target) Is Nothing Then

            Application.ScreenUpdating = False

            Application.EnableEvents = False

            For Each rng In Intersect(Range("C:C"), Target)

                rng.Offset(1).Resize(2).EntireRow.Hidden = (rng.Value <> "Yes")

            Next rng

            Application.EnableEvents = True

            Application.ScreenUpdating = True

        End If

    End Sub

    Switch back to Excel.

    Save the workbook as a macro-enabled workbook (.xlsm).

    Make sure that you allow macros when you open the workbook.

    Was this answer helpful?

    0 comments