Forum Discussion

PeterWallinger's avatar
PeterWallinger
Copper Contributor
Nov 19, 2021

Hiding Row depending on Drop down list

Hi,

 

I have a spreadsheet I am trying to create where I have a range of 7 different values in a drop down list. What I to happen is when I select a certain entry from the drop down list that a number of different rows on the spreadsheet will be hidden depending on the entry selected from the drop down. Trying to get it to do this automatically when the drop down selection is changed. I know this is to be done through VBA but a bit of help as to how to start getting this rolling would be greatly appreciated.

 

Many Thanks

Macros and VBA

2 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Nov 19, 2021

    PeterWallinger 

    Let's say the dropdown is in B1, and its values are Text1, Text2, ..., Text7.

    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)
    ' Has the cell with the dropdown changed?
    If Not Intersect(Range("B1"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        ' Unhide all rows in the relevant range
        Range("A4:A59").EntireRow.Hidden = False
        ' Inspect the value of the dropdown
        Select Case Range("B2").Value
            Case "Text1"
                Range("A4:A11").EntireRow.Hidden = True
            Case "Text2"
                Range("A12:A19").EntireRow.Hidden = True
            Case "Text3"
                Range("A20:A27").EntireRow.Hidden = True
            Case "Text4"
                Range("A28:A35").EntireRow.Hidden = True
            Case "Text5"
                Range("A36:A43").EntireRow.Hidden = True
            Case "Text6"
                Range("A44:A51").EntireRow.Hidden = True
            Case "Text7"
                Range("A52:A59").EntireRow.Hidden = True
        End Select
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

    Change the code as needed.

    Switch back to Excel.

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

    Make sure that you allow macros when you open it.

Resources