+ Reply to Thread
Results 1 to 17 of 17

Hide/Unhide Columns based on Cell Value

  1. #1
    Registered User
    Join Date
    07-24-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    11

    Hide/Unhide Columns based on Cell Value

    I'm looking to hide/unhide a whole column based on a value in a cell in that column. I would prefer to do this automatically whenever there is a change in the sheet. I can find a lot of examples of how to do this in a row but columns escape me. So any help would be great.

    Thanks in advance.

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,000

    Re: Hide/Unhide Columns based on Cell Value

    Could the value be in any column or is it in one particular column? This code will hide any column in which you enter an "x". It is a Worksheet_Change event so it must be placed in the code module for the worksheet.
    Please Login or Register  to view this content.
    Last edited by Mumps1; 07-24-2013 at 11:25 AM.

  3. #3
    Registered User
    Join Date
    07-24-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Hide/Unhide Columns based on Cell Value

    Thanks for the reply. I found one that "works" but it doesn't auto-update. What I am trying to do is have a drop down to select "# of Floors" but when I change the value of that, which resides in cell Z1, the columns don't hide.

    Please Login or Register  to view this content.

  4. #4
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,000

    Re: Hide/Unhide Columns based on Cell Value

    Just to clarify ... You have a drop down list in Z1 where you choose "# of Floors". Are columns hidden based on the value of Z1 and if so, what is that value and which columns should be hidden? Are columns hidden based on some cells being zero and if so, what is the range of cells and which columns should be hidden. You include the range "F3:Y2". Did you mean "F2:Y3"? It's not quite clear from your description exactly what you want to do. Also, under what conditions do you want the columns to be unhidden? Perhaps you could use a specific example to describe what you would like or post a copy of your file with a detailed description.

  5. #5
    Registered User
    Join Date
    07-24-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Hide/Unhide Columns based on Cell Value

    Quote Originally Posted by Mumps1 View Post
    Just to clarify ... You have a drop down list in Z1 where you choose "# of Floors". Are columns hidden based on the value of Z1 Yes and if so, what is that value and which columns should be hidden? The value is a list with whole numbers 1 - 50 Are columns hidden based on some cells being zero and if so, what is the range of cells and which columns should be hidden. No You include the range "F3:Y2". Did you mean "F2:Y3"? Yes hit the 2 instead of the 3 It's not quite clear from your description exactly what you want to do. Also, under what conditions do you want the columns to be unhidden? I would like them unhidden in the event I change the # of floors. Perhaps you could use a specific example to describe what you would like or post a copy of your file with a detailed description.
    I hope that clarifies some. I'll post the workbook I've been working in.

  6. #6
    Registered User
    Join Date
    07-24-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Hide/Unhide Columns based on Cell Value

    EST3 EST3x IO Sheet.xlsm This shows what I have been trying to do. The main thing I want to achieve right now is that auto-update of the # of floors to hide/unhide those columns. The rest of it is functionality that is going to take some more doing.

  7. #7
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,000

    Re: Hide/Unhide Columns based on Cell Value

    I can't seem to find the location of the drop down list.

  8. #8
    Registered User
    Join Date
    07-24-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Hide/Unhide Columns based on Cell Value

    Do you mean the data for the drop down list? If thats the case its being done with data verification drop down. The drop down otherwise is on the FA sheet next to the text "# of Floors".

  9. #9
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,000

    Re: Hide/Unhide Columns based on Cell Value

    When you choose a number from the drop down list in cell BR1, you want the corresponding columns in row 2, columns F to BC, to be hidden. For example, if you select the number 5, then columns F to J would be hidden. Then if you choose the number 3, only columns from F to H would be hidden. Is this correct?

  10. #10
    Registered User
    Join Date
    07-24-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Hide/Unhide Columns based on Cell Value

    Yes absolutly.

  11. #11
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,000

    Re: Hide/Unhide Columns based on Cell Value

    OK. I have to go out for a bit now, but when I get back, I'll give you some feedback.

  12. #12
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,000

    Re: Hide/Unhide Columns based on Cell Value

    Place this maco into the code module for worksheet "FA". You can do this by right clicking on the tab for the worksheet, click 'View Code', paste the macro into the empty code module that appears and then close the code module window to return to the worksheet. Now choose a number in the drop down menu in cell BR1. Please let me know how it works out.
    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    07-24-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Hide/Unhide Columns based on Cell Value

    Works perfect but only its backwards.
    Last edited by dberg; 07-25-2013 at 09:10 PM.

  14. #14
    Registered User
    Join Date
    07-24-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Hide/Unhide Columns based on Cell Value

    Okay I modified the code a little and I got it work the way I wanted it to.

    Please Login or Register  to view this content.

  15. #15
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,000

    Re: Hide/Unhide Columns based on Cell Value

    Looking at your revised code, I can't see how it's working properly for you. You said that the original code I suggested is working well except that it is backwards. I assume that you mean that rather than hiding the number of columns you enter in BR1, you want to make them visible and hide the rest of the columns. The code below should do that for you if that's what you were looking for. Also, you have a zero value in column E and 51 and 52 in columns BD and BE. They will remain visible. Please let me know if everything is working out for you or if the code needs to be tweeked a little more.
    Please Login or Register  to view this content.

  16. #16
    Registered User
    Join Date
    07-24-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Hide/Unhide Columns based on Cell Value

    Okay I have changed things around again and changed how I was setting the columns to be hidden. I also added in the function to hide rows as well.

    Please Login or Register  to view this content.
    Thank you for all your help.

  17. #17
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,000

    Re: Hide/Unhide Columns based on Cell Value

    My pleasure.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Checkbox VBA - Hide/Unhide Rows and/or Columns based on cell values
    By gravy258 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-21-2013, 05:39 PM
  2. Hide and unhide columns based on value of 0 or 1 (respectively) in cells G1:LG1
    By cplawren in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-22-2012, 01:58 AM
  3. Hide/Unhide columns based on value of a cell
    By redrackham in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-24-2011, 09:14 AM
  4. Hide & Unhide columns based on a cell value
    By DaveNUFC in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-21-2010, 09:40 AM
  5. [SOLVED] Hide or Unhide certain columns based on a cell value
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-10-2006, 01:04 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1