Hiding Rows Based On Value

Hello,

I have the following three macros that hide columns based on a range that has formulas in it flagging x's based on dates. The code works fine it is just very slow because it is going through cell by cell looking for an X. Is there a quicker way to write this so it hides columns with an X? Would I also be able to use that code similarly but hiding rows based on values down the row with x? I cannot use filters for this. It is a template that users need to be able to toggle back and forth. Grouping doesn't work because there is a macro to add rows and when the user does so it breaks the grouping so that it no longer groups correctly.

Please Login or Register  to view this content.

As always, Range.Find is your friend.

Here's an example for ShowQtr:

Please Login or Register  to view this content.

CAn you post the workbook, or an example workbook that replicates the problem?

sure. See attached. There is a formula in rows 1 and two that runs a test on the period the workbook is for and drops x's in order to switch between the current quarter, month, year.

The code in place works it just takes a long time to complete and you can see the screen flash while it works. I would like it to be almost instant where the user doesn't even recognize it is moving through code.

Please Login or Register  to view this content.

I didn't know there were formulas, and I missed that they could be capital X's, my bad.

This runs in under 3/100ths of a second on my machine in your example you posted, so it shouldn't be noticeable.

Please Login or Register  to view this content.

Thanks again. It still is not working on my end. It runs fast but it doesn't hide anything.

Do you have the code pasted in the right sheet object? Should be in Sheet1 in your example. Not sure what else. Runs fine for me, i see columns between D and CM hidden after the code runs.

Thanks PCI. That doesn't seem to do anything either. I am going to reboot and see if the server I am logged into is having issues.

I added it to the sheet itself as well as in a module and tried both. It runs but nothing changes. The file is 5,514kb otherwise I would post the live file so you could see.

it is working now walruseggman but in reverse. It is hiding the columns with X's. I want it to hide the columns with " " instead. I will try changing that and let you know.

That didn't work. I can try changing the formula around so that it drops an X in the columns to hide and leaves the one to remain visible blank.

Changing the formula around to put X's on columns to hide instead got your code to work the way I wanted. Thank you very much!

PCI that change you suggested also worked. Thank you both again!