Conditionally Hide Columns based on cell result

Hello. I am new to manipulating excel via VBA. I am trying to conditionally hide an entire coloumn based on a cell result in the given column. I have gotten only the below code to work.

Please Login or Register  to view this content.

I can get this to hide coloumn B as I want, but it only works for coloumn B (obviously). I want it to work for a range of coloumns from B to BA such that B3=0-->hide colB , C3=""-->display col C , D3=0-->hide colD , ... etc. up to coloumn BA

I'm assuming the answer is as simple as nesting the above code in a while loop or something similar from B:BA, but I don't have the know-how to program such a macro.

Thanks in Advance!

This is one option

Please Login or Register  to view this content.

Hi Dave;
Shouldn't this line

Originally Posted by davesexcel

Please Login or Register  to view this content.

Be

Please Login or Register  to view this content.

I'm also curious. Mose people use Intersect() instead of Union(). Is there a reason you don't?

This code is run after any event in Excel correct?

Not all code is run after every event.
1) Code has to be in the sheet's module to run after an event. (Right Mouse click the sheet's tab and select "View Code")
2) The different macros run after different events.
Worksheet_Change runs after you change any cell's value (or a macro changes a cell's value).
Worksheet_SelectionChange runs after you change selection in the sheet.
Etc.

So it depends on when you want the macro to run. If you want it to constantly be checking to see if cells B3:BA3 have changed put it into Worksheet_SelectionChange, but if you only want to check if B3:BA3 has been changed then put into Worksheet_Change
I'm pretty sure that everyone would recommend you put it into Worksheet_Change

Originally Posted by montego

I'm still curious why the Range its referencing are rows 1:100, but, it seems to be working
Any one care to elaborate on why the B1:B100 Range Works?

You will discover that it hides the column when you change ANY cell in rows 1:100 to 0.
If you want it to hide the column only when row 3 is changed to 0 then use B3:BA3

I was messing around and noticed that alhtough the code worked the first time, when I scrolled over it hid every coloumn from B:BA, regardless of what was in row 3 of each coloumn. Deleted code in VBA editor, repasted it, scrolled through the sheet again and seems to be working.

Overall it looks like the code works, but gets screwed up when I manually unhide hidden columns by right clicking and then unhide. Deleting the Code and Repasting seems to fix it.

Originally Posted by montego

I was messing around and noticed that alhtough the code worked the first time, when I scrolled over it hid every coloumn from B:BA, regardless of what was in row 3 of each coloumn

Without seeing your workbook, I would guess that the line

Please Login or Register  to view this content.

is not working right.

Originally Posted by montego

Hmm, Well where does it reference Row 3 to check against?

Please Login or Register  to view this content.

Will hide the column when ANY cell in rows 1:100 changes to 0

Please Login or Register  to view this content.

Will hide the column only when row 3 changes to 0

Hi montego;

FYI: after a column is hidden, if you want to unhide it, just have a macro change the contents of the cell in row 3 to "" or any other value except 0. This will trigger Worksheet_Change again and unhide the column

Originally Posted by montego

Foxguy: Thank you for all your help and your extremely quick responses. I 'approved' your response on your first post, but I am unable to do it for the other posts because I'm a newb. Anything else I can do for you?

Nope, that does it. I'm on my way out the door. If any thing else comes up, I'll be back in 4 hours.

Originally Posted by foxguy

Hi montego;

FYI: after a column is hidden, if you want to unhide it, just have a macro change the contents of the cell in row 3 to "" or any other value except 0. This will trigger Worksheet_Change again and unhide the column

So now I'm thinking I should have two buttons on the sheet. One would be "Show relevant Data"

Please Login or Register  to view this content.

and It would be linked to the Current code Dave poste, Something Like

Please Login or Register  to view this content.

Then the second Button would be "Show All Cells"

Please Login or Register  to view this content.

here is what I am thinking

Please Login or Register  to view this content.

Hi montego;

Please Login or Register  to view this content.

Hi montego;

You have the 2 macros mixed up. This is what you should have assigned to a button.

Please Login or Register  to view this content.

This is the code for Worksheet_Change with the name changed.

Please Login or Register  to view this content.

BTW the error you are getting is saying that the Macro is expecting a "Target", that you can't pass directly from a button.

Hi montego;

Please Login or Register  to view this content.

This sub will never hide any columns. It needs a Target passed to it. The code works in Sub Worksheet_Change(ByVal Target as Range) because the cell that is being changed is passed to the sub as Target.
I assume you get an error when you click "CommandButton1"
You need to define Target. Since the caption for the button is "Relevant Data" you need to tell the macro what columns are the Relevant Data. Something like this

Please Login or Register  to view this content.

Based on your picture I can't tell what the Relevant Data is. If you have more that 1 Relevant Data (like maybe the Relevant Data changes with the Date, or Part No, or Time of Day, etc.) then 1 button might have a lot of trouble unhiding ONLY the Relevant Data.

Is this not a solved thread?

Originally Posted by davesexcel

Is this not a solved thread?

The original question was answered and he marked it solved, but then realized that he really wanted more that he thought. He tried to improve on the answer himself, but got confused, so he asked me to help with the addition.

Originally Posted by montego

I can make a new thread If both of you think that is appropriate

I have no problem with leaving it the way it is. This has happened to me before, and this is the first time that a moderator has said anything about it.

My thoughts are that since the 2nd question is directly related to the original solution, I would leave it here. Any one who looks at the thread because they saw the title, could very easily be interested in the follow up, but I'll follow the moderator's advice.