SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2717150%22%20slang%3D%22en-US%22%3EShow%20or%20hide%20columns%20based%20on%20content%20in%20row%20%2F%20drop-down%20selection%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2717150%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%26nbsp%3B%20I%20have%20an%20excel%20document%20that%20has%20details%20on%20types%20of%20plans%20and%20then%20whether%20the%20plan%20is%20offered%20in%20a%20specific%20county.%26nbsp%3B%20Each%20plan%20is%20a%20column%20and%20each%20County%20is%20in%20it's%20own%20row%20with%20%22Y%22%20or%20%22N%22%20under%20each%20county%2C%20to%20show%20whether%20the%20plan%20is%20available%20in%20that%20county.%26nbsp%3B%20I%20would%20like%20to%20set%20it%20up%20so%20that%20if%20I%20select%20a%20County%20in%20a%20drop-down%20list%2C%20it%20hides%20the%20columns%20that%20have%20the%20%22N%22%20value%20for%20that%20county's%20row.%26nbsp%3B%20Is%20this%20possible%3F%26nbsp%3B%20I've%20attached%20a%20screenshot%20of%20the%20document%2C%20so%20you%20have%20a%20visual%20of%20what%20I'm%20trying%20to%20do.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20note%20I%20am%20using%20Excel%20on%20a%20Mac.%26nbsp%3B%20Thanks%20very%20much%20for%20any%20help%20you%20can%20provide!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2717150%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2717286%22%20slang%3D%22en-US%22%3ERe%3A%20Show%20or%20hide%20columns%20based%20on%20content%20in%20row%20%2F%20drop-down%20selection%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2717286%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1145935%22%20target%3D%22_blank%22%3E%40lisacoleman%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERight-click%20the%20sheet%20tab.%3C%2FP%3E%0A%3CP%3ESelect%20'View%20Code'%20from%20the%20context%20menu.%3C%2FP%3E%0A%3CP%3ECopy%20the%20code%20listed%20below%20into%20the%20worksheet%20module.%3C%2FP%3E%0A%3CP%3EI%20made%20some%20assumptions%20based%20on%20your%20screenshot%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EThe%20cell%20with%20the%20dropdown%20is%20G2.%3C%2FLI%3E%0A%3CLI%3EThe%20counties%20are%20listed%20in%20column%20A.%3C%2FLI%3E%0A%3CLI%3EThe%20relevant%20columns%20are%20B%20to%20E.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3EIf%20they%20are%20incorrect%20you'll%20have%20to%20modify%20the%20code.%3C%2FP%3E%0A%3CP%3ESwitch%20back%20to%20Excel.%3C%2FP%3E%0A%3CP%3ESave%20the%20workbook%20as%20a%20macro-enabled%20workbook%20(.xlsm).%3C%2FP%3E%0A%3CP%3EMake%20sure%20that%20you%20allow%20macros%20when%20you%20open%20it.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0A%20%20%20%20Dim%20c%20As%20Long%0A%20%20%20%20Dim%20r%20As%20Long%0A%20%20%20%20If%20Not%20Intersect(Range(%22G2%22)%2C%20Target)%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20%20%20%20%20Range(%22B1%3AE1%22).EntireColumn.Hidden%20%3D%20False%0A%20%20%20%20%20%20%20%20If%20Range(%22G2%22).Value%20%26lt%3B%26gt%3B%20%22%22%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20r%20%3D%20Range(%22A%3AA%22).Find(What%3A%3DRange(%22G2%22).Value%2C%20LookAt%3A%3DxlWhole).Row%0A%20%20%20%20%20%20%20%20%20%20%20%20For%20c%20%3D%202%20To%205%20'%20columns%20B%20to%20E%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20If%20Cells(r%2C%20c).Value%20%3D%20%22N%22%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Cells(r%2C%20c).EntireColumn.Hidden%20%3D%20True%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20%20%20%20%20Next%20c%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20Application.ScreenUpdating%20%3D%20True%0A%20%20%20%20End%20If%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2739768%22%20slang%3D%22en-US%22%3ERe%3A%20Show%20or%20hide%20columns%20based%20on%20content%20in%20row%20%2F%20drop-down%20selection%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2739768%22%20slang%3D%22en-US%22%3EHans%2C%20THANK%20YOU!!%20This%20works%20perfectly%20and%20I%20am%20so%20grateful%20for%20your%20help!%3C%2FLINGO-BODY%3E
New 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 (New 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.