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.

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.

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.

Originally Posted by Mumps1

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.

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.

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

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".

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?

Yes absolutly.

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

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.

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.

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.

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.

My pleasure.