Auto Hide & Unhide Rows in Excel

Question

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

Answer 1

Sorry, I forgot an End If:

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

        Me.Unprotect Password:="secret"

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

            If rng.Value = "Yes" Then

                rng.Offset(1).Resize(2).EntireRow.Hidden = False

                rng.Offset(1).Resize(2).EntireRow.AutoFit

            Else

                rng.Offset(1).Resize(2).EntireRow.Hidden = True

            End If

        Next rng

        Me.Protect Password:="secret"

        Application.EnableEvents = True

        Application.ScreenUpdating = True

    End If

End Sub

Answer 2

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.

Answer 3

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

Answer 4

If the sheet is protected, you have to unprotect it temporarily in the code:

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

        Me.Unprotect Password:="secret"

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

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

        Next rng

        Me.Protect Password:="secret"

        Application.EnableEvents = True

        Application.ScreenUpdating = True

    End If

End Sub

Here, "secret" is the password used to protect the sheet. If you didn't specify a password, use "".

Answer 5

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