Hide columns in an excel file based on cell value

  • Hi all,


    I've been researching for an answer but didn't find one solving my problem yet.


    Context:

    - I'm building a file for team members to enter some data for their category (Sales, Marketing, HR, etc.) per quarter (Q1 2022, Q2 2022, etc)

    - For now: all categories are displayed in columns: Sales from column C to column H, Marketing from column I to columns P, etc. for the other categories (so the file has many columns to fit all categories).


    Objective:

    - I would like to facilitate the usage of the file. Let's say if I write "Sales" in cell A1: I want only column C to H to appear, if I write "Marketing" in cell A1: I want only column I to P to appear, etc.

    - Ideally, I would like the macro to run automatically when the text is entered in A1


    I've tried to create a macro but it's wrong :/ (the columns are hidden when I enter a value in A1, but when I change the text in A1, nothing happens)


    Can someone help please?

  • This macro uses cell B2 for entering a number that activates hiding the columns.

    Study the code and adapt for your use. Paste the entire macro in the Sheet Module :


  • Thank you for the answer but I cannot adapt it properly unfortunately :(


    Anyone else to give me a quick solution? It's basically hiding/unhiding different columns based on a text in a cell

  • Try this


    A1 has a data validation list to select from.

  • Why can't you adapt the code?

    I have tried this and it was not working, will check your proposition


  • Try this

    I'm almost there thank you!!


    Can you please tell me with the code below why when I write "Sales" or "Marketing", I have the left-hands columns that are not hidden ? (the columns on the right are hidden, but the ones on the left side with Supply from "E to I" are still visible and not hidden)


  • Up,


    does someone know what is wrong with my code below? I would really appreciate some help here,


  • That's actually my code!


    Where have you used it?


    Attach an example of your workbook containing the code.

    I've attached the excel file in this message.


    Sorry I've copied paste the wrong code. The code I wanted to post is the one below:


    Files

    • projects.xlsm

      (20.69 kB, downloaded 8 times, last: )
  • You have MergedCells that don't allow the columns to be hidden as you expect.


    You should NEVER use MergedCells, they will invariably cause problems.


    Why have you got multiple entries for 2021 Q4?


    Also, why haven't you used the Data Validation List that I suggested?

  • Oh ok thanks for that, I didn't know.


    It's still not working though, do you know why when I type "Marketing", it doesn't show the desired columns? I guess there is something wrong :


    Case "Marketing"

    Columns("J:N").EntireColumn.Hidden = False

    Columns("E:I,O:BC").EntireColumn.Hidden = True


    full code:

  • You need to remove the the merged cells and replace with Center Across Selection. I have done this for the first 3 departments.


    Check the amended code

  • You need to remove the the merged cells and replace with Center Across Selection. I have done this for the first 3 departments.


    Check the amended code

    thank you so much! there is one last problem though: every time I modify a cell in excel: all columns appear back automatically, any idea why?

  • I didn't notice before but you have the code in Module 1 and the WorkBook event. I edited the code in Module 1 before

  • I didn't notice before but you have the code in Module 1 and the WorkBook event. I edited the code in Module 1 before

    THANK YOU ! working perfectly :)

  • Pleased to help.


    Post back if you need further help.


    Visit my web site, http://www.excel-it.com, for more examples and some helpful articles.