$25 USD: Hide columns if cells "contain" specific text

  • I am looking for a macro that will hide columns in a sheet if cells in Row 1 contain specific text. For example, if ROW 1 has a cell which CONTAINS the word "Main", I would like to the macro to hide the column. There may be other text in cell separated by semicolons. In the table below, I would like columns A, C and D to be hidden.


    [TABLE="class: grid, width: 500, align: center"]

    [tr]


    [td][/td]


    [TD="align: center"]A[/TD]
    [TD="align: center"]B[/TD]
    [TD="align: center"]C[/TD]
    [TD="align: center"]D[/TD]

    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td]

    Main

    [/td]


    [td][/td]


    [td]

    Main; USD

    [/td]


    [td]

    Main; USD; Trend

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]


    Pre-payment has been sent.

  • Re: $25 USD: Hide columns if cells "contain" specific text


    Hi, I will take this on. Should have it in about 8 hours.... Is that ok?

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________

  • Re: $25 USD: Hide columns if cells "contain" specific text


    After thinking about this, I would like to revise and add the some criteria. I would like this macro to run on columns A to FW only. And I would like the macro to UN-HIDE any column in in ROW 1 that CONTAINS the word "Main" in a cell and HIDE the columns that DO NOT CONTAIN the word "Main". So in this example, columns A, C and D will be visible and B and all other columns without "Main" will be HIDDEN.



    [TABLE="class: cms_table_grid, width: 500, align: center"]

    [tr]


    [td]

    [/td]


    [TD="align: center"]A[/TD]
    [TD="align: center"]B[/TD]
    [TD="align: center"]C[/TD]
    [TD="align: center"]D[/TD]

    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td]

    Main

    [/td]


    [td]

    [/td]


    [td]

    Main; USD

    [/td]


    [td]

    Main; USD; Trend

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [/TABLE]

    Let me know if you have any ?'s

  • Re: $25 USD: Hide columns if cells "contain" specific text


    No Problem... :)


    See attached and code below. Let me know if you have any questions. I added another macro to unhide all columns. Its available in the workbook. Not sure if it will be of any use to you or not. ;)





    Regards,
    Ger

    Files

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________

  • Re: $25 USD: Hide columns if cells "contain" specific text


    One more question. In my sheet I have some column groupings. Is it possible to add some code that will close all column groupings as well after the columns are hidden? The macro seems to open all groupings after running. Thanks!!!

  • Re: $25 USD: Hide columns if cells "contain" specific text


    Funds received! Thank you.


    Could you send me an example of what you mean and I can have a look... you can remove any sensitive data and just leave five or six rows of fake data.


    Thanks,
    Ger

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________

  • Re: $25 USD: Hide columns if cells "contain" specific text


    Hi GoCavs... this seems to work, but slows it down a little



    If [speed] becomes a problem, I can look at it again on Tuesday (sorry, just heading away for a long weekend)


    Ger

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________