$50 USD: Hide / Unhide columns based on cell value

  • 10% pre-payment made:


    I am looking for a macro that will hide/unhide a set of columns in a range based on cell value (A2). I have a number of Autofilter macro buttons which assign cell A2 a value when they are clicked. These buttons are on top of the sheet in a section called "BU-COM Filter". For example, if the "Americas" button is clicked then the string called "Americas" will be input to cell A2 by the macro. Then this will trigger specific columns to be hidden. The column range that I would the macro to consider is columns S through AK. I attached a sample file to work with.


    When A2 is automatically updated to the following values, I would like these columns hidden (see table below). Maybe row 1 can be used as a helper column of some sort??




    [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 106"] [/TD]
    [TD="width: 216"]Range("S:AK")[/TD]
    [TD="width: 510"] [/TD]

    [/tr]


    [tr]


    [td]

    Cell A2 value

    [/td]


    [td]

    EntireColumn.Hidden = True

    [/td]


    [td]

    Note

    [/td]


    [/tr]


    [tr]


    [td]

    Null

    [/td]


    [td]

    S:AK

    [/td]


    [td]

    If A2 = NULL or "", then unhide entire range (S:AK)

    [/td]


    [/tr]


    [tr]


    [td]

    Americas

    [/td]


    [td]

    T:U, Z:AK

    [/td]


    [td]

    Hide any column that DOES NOT CONTAIN "Americas" in Row 10

    [/td]


    [/tr]


    [tr]


    [td]

    EMEA

    [/td]


    [td]

    S, U, V:Y, AD:AK

    [/td]


    [td]

    Hide any column that DOES NOT CONTAIN "EMEA" in Row 10

    [/td]


    [/tr]


    [tr]


    [td]

    Asia

    [/td]


    [td]

    A:T, V:AC, AH:AK

    [/td]


    [td]

    Hide any column that DOES NOT CONTAIN "Asia" in Row 10

    [/td]


    [/tr]


    [tr]


    [td]

    EI

    [/td]


    [td]

    S:U, W:Y, AA:AC, AE:AG, AI:AK

    [/td]


    [td]

    Hide any column that DOES NOT CONTAIN "EI" in Row 10

    [/td]


    [/tr]


    [tr]


    [td]

    FAS

    [/td]


    [td]

    S:V, X:Z, AB:AD, AF:AH, AJ:AK

    [/td]


    [td]

    Hide any column that DOES NOT CONTAIN "FAS" in Row 10

    [/td]


    [/tr]


    [tr]


    [td]

    DIG

    [/td]


    [td]

    S:W, Y:AA, AC:AE, AG:AI, AK

    [/td]


    [td]

    Hide any column that DOES NOT CONTAIN "DIG" in Row 10

    [/td]


    [/tr]


    [tr]


    [td]

    TRS

    [/td]


    [td]

    S:X, Z:AB, AD:AF, AH:AJ

    [/td]


    [td]

    Hide any column that DOES NOT CONTAIN "TRS" in Row 10

    [/td]


    [/tr]


    [tr]


    [td]

    Americas-EI

    [/td]


    [td]

    T:U, W:AK

    [/td]


    [td]

    Hide any column that DOES NOT EQUAL "Americas" OR "Americas-EI" in Row 10

    [/td]


    [/tr]


    [tr]


    [td]

    Americas-FAS

    [/td]


    [td]

    T:V, X:AK

    [/td]


    [td]

    Hide any column that DOES NOT EQUAL "Americas" OR "Americas-FAS" in Row 10

    [/td]


    [/tr]


    [tr]


    [td]

    Americas-DIG

    [/td]


    [td]

    T:W, Y:AK

    [/td]


    [td]

    Hide any column that DOES NOT EQUAL "Americas" OR "Americas-DIG" in Row 10

    [/td]


    [/tr]


    [tr]


    [td]

    Americas-TRS

    [/td]


    [td]

    T:X, Z:AK

    [/td]


    [td]

    Hide any column that DOES NOT EQUAL "Americas" OR "Americas-TRS" in Row 10

    [/td]


    [/tr]


    [tr]


    [td]

    EMEA-EI

    [/td]


    [td]

    S, U:Y, AA:AK

    [/td]


    [td]

    Hide any column that DOES NOT EQUAL "EMEA" OR "EMEA-EI" in Row 10

    [/td]


    [/tr]


    [tr]


    [td]

    EMEA-FAS

    [/td]


    [td]

    S, U:Z, AB:AK

    [/td]


    [td]

    Hide any column that DOES NOT EQUAL "EMEA" OR "EMEA-FAS" in Row 10

    [/td]


    [/tr]


    [tr]


    [td]

    EMEA-DIG

    [/td]


    [td]

    S, U:AA, AC:AK

    [/td]


    [td]

    Hide any column that DOES NOT EQUAL "EMEA" OR "EMEA-DIG" in Row 10

    [/td]


    [/tr]


    [tr]


    [td]

    EMEA-TRS

    [/td]


    [td]

    S, V:AB, AD:AK

    [/td]


    [td]

    Hide any column that DOES NOT EQUAL "EMEA" OR "EMEA-TRS" in Row 10

    [/td]


    [/tr]


    [tr]


    [td]

    Asia-EI

    [/td]


    [td]

    S:T, V:AC, AE:AK

    [/td]


    [td]

    Hide any column that DOES NOT EQUAL "Asia" OR "Asia-EI" in Row 10

    [/td]


    [/tr]


    [tr]


    [td]

    Asia-FAS

    [/td]


    [td]

    S:T, V:AD, AF:AK

    [/td]


    [td]

    Hide any column that DOES NOT EQUAL "Asia" OR "Asia-FAS" in Row 10

    [/td]


    [/tr]


    [tr]


    [td]

    Asia-DIG

    [/td]


    [td]

    S:T, V:AE, AG:AK

    [/td]


    [td]

    Hide any column that DOES NOT EQUAL "Asia" OR "Asia-DIG" in Row 10

    [/td]


    [/tr]


    [tr]


    [td]

    Asia-TRS

    [/td]


    [td]

    S:T, V:AF, AH:AK

    [/td]


    [td]

    Hide any column that DOES NOT EQUAL "Asia" OR "Asia-TRS" in Row 10

    [/td]


    [/tr]


    [/TABLE]

  • Quick question : is the range for Asia starts at column A ?
    [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 109"]Asia[/TD]
    [TD="width: 210"]A:T, V:AC, AH:AK[/TD]
    [TD="width: 83"]1[/TD]
    [TD="width: 85"]Asia[/TD]
    [TD="width: 88"] [/TD]
    [TD="width: 560"]Hide any column that DOES NOT CONTAIN "Asia" in Row 10[/TD]

    [/tr]


    [/TABLE]

  • Payment received... Thanks !


    Please find attached the file with the macro.


    The code is in the sheet "2019 snapshot" module.
    I renamed your blank sheet to "Param" and put your parameters in a table called "TblParam".


    The logic is as follow:
    - when you make a slection and the value of A2 changes, the macro will :
    - show all columns
    - look at the A2 value in the parameter table
    - use the columns listed in the 2nd column of the table to construct a range
    - hide the columns of that range.


    So, as long as you list correctly all columns to hide in the table, the same way as you did (with : and separated by comma), the macro will work.
    Note: the content of the header row is NOT verified / used in the macro since all columns to hide are listed in the table.


    Let me know if you have any questions.
    Thanks,
    GC