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

Try this version:

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

        Next rng

        Me.Protect Password:="secret"

        Application.EnableEvents = True

        Application.ScreenUpdating = True

    End If

End Sub

Answer 3

I got this error.

Answer 4

You're amazing, Hans!

Thank you so much for your help.

Kind Regards,

Shaynne

Answer 5

Hi, Hans.

I tried to add another column (D) so that I can merge column B & C however, the content in the merged cell doesn't autofit. 

How do I change the code you previously gave me to wrap the text?

Thanks,

Shaynne