Hide rows based on drop down list

Question

I would like to hide certain rows automatically on my worksheet when an item is selected from a drop down list, and unhide these rows for other items are selected from the list. Is this possible?

Answer 1

Sure, you could use the worksheet change event: Copy this code, right-click the sheet tab, select "View Code" and paste the code into the window that appears. Change the cell address, values to act on, and rows to be hidden by each value. I have given you a few different ways of picking the rows....

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub

    If Target.Address <> "$A$2" Then Exit Sub

    Application.EnableEvents = False

    If Target.Value = "This" Then

        Cells.EntireRow.Hidden = False

        Rows(3).Hidden = True

        Rows("6:9").Hidden = True

        Range("A12").EntireRow.Hidden = True

    End If

    If Target.Value = "That" Then

        Cells.EntireRow.Hidden = False

        Rows(4).Hidden = True

        Rows("10:11").Hidden = True

        Range("A14").EntireRow.Hidden = True

    End If

    Application.EnableEvents = True

End Sub

Bernie

Answer 2

Hi,

I'm a VBA novice and have been trying to automatically hide/unhide individual rows on a worksheet according to multiple values within a dropdown list (created via data validation). Techexpert78's code works brilliantly and I wondered if someone would be kind enough to modify it to hide specific individual rows according to multiple values within a list? I'm using excel 2013.

My sheet is 'Sheet1' and the data list exists within cell B1. The multiple values are: "LV56 MUB", "BJ60 GVE", "GY09 ODU" and "DY08 HFB". When cell B1 equals any of those values; I would like rows 8, 20, 28, 29, 32 and 34-38 to be hidden, only unhiding when B1 is blank or equals any other value.

Many thanks

Answer 3

I am using the following code base on your suggestion above.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub

    If Target.Address <> "$E$8" Then Exit Sub

    Application.EnableEvents = False

    If Target.Value = "Problem 1" Then

        Cells.EntireRow.Hidden = False

        Rows("16:33").Hidden = True

    End If

    If Target.Value = "Problem 2" Then

        Cells.EntireRow.Hidden = False

        Rows("10:15").Hidden = True

        Rows("22:33").Hidden = True

    End If

    If Target.Value = "Problem 3" Then

        Cells.EntireRow.Hidden = False

        Rows("10:21").Hidden = True

        Rows("28:33").Hidden = True

    End If

    If Target.Value = "Problem 4" Then

        Cells.EntireRow.Hidden = False

        Rows("10:27").Hidden = True

    End If

    If Target.Value = "Test" Then

        Cells.EntireRow.Hidden = False

        Rows("10:33").Hidden = False

    End If

    Application.EnableEvents = True

End Sub

In addition I need cell A10 to hide rows 11:15 when false and 12:15 when true

In addition I need cell A11 to hide rows 12:15 when false and 13:15 when true

In addition I need cell A10 to hide rows 13:15 when false and 14:15 when true

ECT

I tried to modify this but had no success. Idea is they select a problem and are presented with first item to try. They check a check box linked to A10 when completed and are presented with next step to try.

Answer 4

This is even smarter:

Private Sub Worksheet_Change(ByVal Target As Range)

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

        With Range("$C$4")

            Range("24:37").EntireRow.Hidden = .Value = "New Value1"

            Range("24:37").EntireRow.Hidden = .Value = "New Value2"

        End With

    End If

End Sub