Forum Discussion

lisacoleman's avatar
lisacoleman
Copper Contributor
Sep 03, 2021
Solved

Show or hide columns based on content in row / drop-down selection

Hello!  I have an excel document that has details on types of plans and then whether the plan is offered in a specific county.  Each plan is a column and each County is in it's own row with "Y" or "N" under each county, to show whether the plan is available in that county.  I would like to set it up so that if I select a County in a drop-down list, it hides the columns that have the "N" value for that county's row.  Is this possible?  I've attached a screenshot of the document, so you have a visual of what I'm trying to do.

 

Please note I am using Excel on a Mac.  Thanks very much for any help you can provide!

 

excel
Excel on Mac
Formulas and Functions
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Sep 03, 2021

    lisacoleman 

    Right-click the sheet tab.

    Select 'View Code' from the context menu.

    Copy the code listed below into the worksheet module.

    I made some assumptions based on your screenshot:

    • The cell with the dropdown is G2.
    • The counties are listed in column A.
    • The relevant columns are B to E.

    If they are incorrect you'll have to modify the code.

    Switch back to Excel.

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

    Make sure that you allow macros when you open it.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Long
    Dim r As Long
    If Not Intersect(Range("G2"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Range("B1:E1").EntireColumn.Hidden = False
        If Range("G2").Value <> "" Then
            r = Range("A:A").Find(What:=Range("G2").Value, LookAt:=xlWhole).Row
            For c = 2 To 5 ' columns B to E
                If Cells(r, c).Value = "N" Then
                    Cells(r, c).EntireColumn.Hidden = True
                End If
            Next c
        End If
        Application.ScreenUpdating = True
    End If
End Sub

     

3 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Sep 03, 2021

    lisacoleman 

    Right-click the sheet tab.

    Select 'View Code' from the context menu.

    Copy the code listed below into the worksheet module.

    I made some assumptions based on your screenshot:

    • The cell with the dropdown is G2.
    • The counties are listed in column A.
    • The relevant columns are B to E.

    If they are incorrect you'll have to modify the code.

    Switch back to Excel.

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

    Make sure that you allow macros when you open it.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Long
    Dim r As Long
    If Not Intersect(Range("G2"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Range("B1:E1").EntireColumn.Hidden = False
        If Range("G2").Value <> "" Then
            r = Range("A:A").Find(What:=Range("G2").Value, LookAt:=xlWhole).Row
            For c = 2 To 5 ' columns B to E
                If Cells(r, c).Value = "N" Then
                    Cells(r, c).EntireColumn.Hidden = True
                End If
            Next c
        End If
        Application.ScreenUpdating = True
    End If
End Sub

     

Resources