Announcement

Collapse
No announcement yet.

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

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

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

  • #2
    Hello,

    I can look at this for you...

    Comment


    • #3
      Thank you GC!!

      Comment


      • #4
        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

        Comment


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

          Comment


          • #6
            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,

            Comment


            • #7
              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

              Comment


              • #8
                Very slick!!! Love the flexibility with the TblParam. THANK YOU!!!!!

                Comment


                • #9


                  Thanks for the feedback !

                  Comment

                  Working...
                  X