Announcement

Collapse
No announcement yet.

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

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • GoCavs
    started a topic $50 USD: Hide / Unhide columns based on cell value

    $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??



    Range("S:AK")
    Cell A2 value EntireColumn.Hidden = True Note
    Null S:AK If A2 = NULL or "", then unhide entire range (S:AK)
    Americas T:U, Z:AK Hide any column that DOES NOT CONTAIN "Americas" in Row 10
    EMEA S, U, V:Y, AD:AK Hide any column that DOES NOT CONTAIN "EMEA" in Row 10
    Asia A:T, V:AC, AH:AK Hide any column that DOES NOT CONTAIN "Asia" in Row 10
    EI S:U, W:Y, AA:AC, AE:AG, AI:AK Hide any column that DOES NOT CONTAIN "EI" in Row 10
    FAS S:V, X:Z, AB:AD, AF:AH, AJ:AK Hide any column that DOES NOT CONTAIN "FAS" in Row 10
    DIG S:W, Y:AA, AC:AE, AG:AI, AK Hide any column that DOES NOT CONTAIN "DIG" in Row 10
    TRS S:X, Z:AB, AD:AF, AH:AJ Hide any column that DOES NOT CONTAIN "TRS" in Row 10
    Americas-EI T:U, W:AK Hide any column that DOES NOT EQUAL "Americas" OR "Americas-EI" in Row 10
    Americas-FAS T:V, X:AK Hide any column that DOES NOT EQUAL "Americas" OR "Americas-FAS" in Row 10
    Americas-DIG T:W, Y:AK Hide any column that DOES NOT EQUAL "Americas" OR "Americas-DIG" in Row 10
    Americas-TRS T:X, Z:AK Hide any column that DOES NOT EQUAL "Americas" OR "Americas-TRS" in Row 10
    EMEA-EI S, U:Y, AA:AK Hide any column that DOES NOT EQUAL "EMEA" OR "EMEA-EI" in Row 10
    EMEA-FAS S, U:Z, AB:AK Hide any column that DOES NOT EQUAL "EMEA" OR "EMEA-FAS" in Row 10
    EMEA-DIG S, U:AA, AC:AK Hide any column that DOES NOT EQUAL "EMEA" OR "EMEA-DIG" in Row 10
    EMEA-TRS S, V:AB, AD:AK Hide any column that DOES NOT EQUAL "EMEA" OR "EMEA-TRS" in Row 10
    Asia-EI S:T, V:AC, AE:AK Hide any column that DOES NOT EQUAL "Asia" OR "Asia-EI" in Row 10
    Asia-FAS S:T, V:AD, AF:AK Hide any column that DOES NOT EQUAL "Asia" OR "Asia-FAS" in Row 10
    Asia-DIG S:T, V:AE, AG:AK Hide any column that DOES NOT EQUAL "Asia" OR "Asia-DIG" in Row 10
    Asia-TRS S:T, V:AF, AH:AK Hide any column that DOES NOT EQUAL "Asia" OR "Asia-TRS" in Row 10
    Attached Files

  • GCExcel
    replied
    Thanks for the feedback !

    Leave a comment:


  • GoCavs
    replied
    Very slick!!! Love the flexibility with the TblParam. THANK YOU!!!!!

    Leave a comment:


  • GCExcel
    replied
    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
    Attached Files

    Leave a comment:


  • GCExcel
    replied
    Hello,
    I have a solution ready. I will send you payment details via PM and then send you the file with the code and the explantations.
    Thanks,

    Leave a comment:


  • GoCavs
    replied
    oops...that was a typo. It should be
    Asia S:T, V:AC, AH:AK

    Leave a comment:


  • GCExcel
    replied
    Quick question : is the range for Asia starts at column A ?
    Asia A:T, V:AC, AH:AK 1 Asia Hide any column that DOES NOT CONTAIN "Asia" in Row 10

    Leave a comment:


  • GoCavs
    replied
    Thank you GC!!

    Leave a comment:


  • GCExcel
    replied
    Hello,

    I can look at this for you...

    Leave a comment:

Working...
X