Hide/Show rows based on the value in a cell

**PatRiot199** · 10-08-2012, 04:14 PM

Hello XL peeps,
I'm a novice with VBA/Macros, but I'm getting better at it with every new challenge I tackle. I currently have a spreadsheet with 2 main tabs. The first tab is for pasting rows of data that in turn is used on the second tab to provide provissioning data provided in steps. I pull all the cells posted on Tab 1 to Tab 2 via formulas, then use data validation to provide a drop down list of sites in cell B4. Based on the value in B4 row 4 populates with the rest of the data via VLOOKUP and I use the following sheet 2 code to call a macro that resets various fields when a new site is selected.

Please Login or Register  to view this content.

I have an requirement now to hide or show 2 ranges of rows based on the value in cell C419, but I can't seem to get Excel to perform the function when I change the value in C419. The default value, which is also reset by the oewSIADnodebREHOMEfiledClear macro, for cell C419 is "Standard Naming". I want to toggle the hidding of rows 455:460 when "Non-Standard Naming" is selected in C419, and hide the rows when "Standard Naming" is selected.

I tried both of the approaches below in a Module, but neither of them has done a thing.

On this one, I tried adding a sub to the sheet code to try and call this macro when cell C419 was changed, but not sure if it is even possible to string Subs together in the sheet code.

Please Login or Register  to view this content.

On this one, I was hoping that just adding this to a macro would work and trigger when the cell in C419 was changed, but it has not worked at all.

Please Login or Register  to view this content.

Thanks in advance for your help.

Patrick

**PatRiot199** · 10-08-2012, 04:58 PM

Just to give the full picture of what is going on here. I do a lot of hidding and unhidding of other rows already and it works fine, but those are triggered from the oewSIADnodebREHOMEfieldClear Macro shown below. Hopefully someone can help me here. Cell C419 is also named "SIADnaming2".

Please Login or Register  to view this content.

**PatRiot199** · 10-08-2012, 08:32 PM

Ok, yeah, I noticed your corrections on the first but wondered if it did that automatically or something. I'm new to this type of forum posting, but I found the code tags and added them. Let me know if this now complies?

**JBeaucaire** · 10-09-2012, 02:37 AM

The simplest worksheet_change syntax I can think of to hide/show rows based on a cell's value:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**PatRiot199** · 10-11-2012, 03:49 PM

Thank you Jerry, I did end up solving the activation of my second dropdown macro trigger within the sheet code with the following:

Please Login or Register  to view this content.

I tried using the cell names for these Macros, since that would allow me to manipulate the overall steps and not have to reset the cell value for my macro, but it doesn't work. Do you know if I'll have to trigger of $C$419, or can I use some variant of "SIADnaming2" for the cell name value and still have this work?

I liked your changes to my macro code, so I updated them and implemented it. I was hoping to see it hopefully complete the macro tasks a bit faster, but it didn't seem to speed up the run of the macro at all. I consider this a solved thread now unless anyone knows how I might be able to make this macro run any faster? If everyone agrees that this is the best performance I'll be able to get with all the hidding and unhidding of the rows, then I'll just have to live with it.

Here's the code I am trying to optimize if possible...

Please Login or Register  to view this content.

Oh yeah, I was unable to use your suggested code for the 2 below because those cells are merged with other cells, but I would prefer to reset them without selecting them if that is possible. Here is the code you suggested next to what I ended up going back to, to get it to work.

Please Login or Register  to view this content.

Again, I would prefer to reset them without having to select the cell, but not sure how to do that since the cell values are merged with other cells, and need to stay that way.

Thanks again Jerry

**JBeaucaire** · 10-11-2012, 04:51 PM

Maybe:

Please Login or Register  to view this content.

**PatRiot199** · 10-11-2012, 10:09 PM

Originally Posted by JBeaucaire

Maybe:

Please Login or Register  to view this content.

Thanks again Jerry, that worked great and keeps from having to select the cell in order to do it.

**JBeaucaire** · 10-11-2012, 10:28 PM

I have marked this thread solved for you.
In the future please select Thread Tools from the menu above and mark the thread as solved. Thanks.

**PatRiot199** · 10-11-2012, 11:52 PM

Ok I will. I kinda wanted to see if someone had any input on whether the code to hide/unhide could be optimized any to speed up the macro run time. Right now it takes 7 seconds to run the "oewSIADnodebREHOMEfieldClear" macro, but I have no choice. I have to hide/unhide based on that criteria so I guess I'll have to live with it.

Thanks again Jerry for your code change advisement and your guidence to comply with the forum rules.