SOLVED

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

Copper Contributor

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!

 

3 Replies
best response confirmed by lisacoleman (Copper Contributor)
Solution

@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

 

Hans, THANK YOU!! This works perfectly and I am so grateful for your help!

@lisacoleman 

You're welcome! Glad to have been able to help.

1 best response

Accepted Solutions
best response confirmed by lisacoleman (Copper Contributor)
Solution

@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

 

View solution in original post