Help on simple hide/unhide but based on multiple cell values - please

Hi,

I am relatively new to vba and learning on the job at the moment. Would be super grateful if someone can help on what will be simple I'm sure for your standards!

I want to unhide certain rows 78:82, if the text 'disc' is inserted into any of the cell range below.

If the text is not contained in any of the cell range, then I want the rows 78:82 to disappear.

Now I think its the range that is causing the issues, but after spending 30 mins on it this morning I cannot find a solution.

CODE

Please Login or Register  to view this content.

A massive thank you in advance for anyone able to help!

I am going to throw two canned replies at you then explain the program.

Canned Reply #1 - please use code tags in the future.
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Canned Reply #2
It is a best practice to declare all variables. If you misspell a variable in your code, VBA will silently assume it is a Variant variable and go on executing with no clue to you that you have a bug. Go to the VBA development window, click Tools, Options, and check "Require Variable Declaration." This will insert the following line at the top of all new modules:

Please Login or Register  to view this content.

This option requires all variables to be declared and will give a compiler error for undeclared variables.

I can't say enough good about Option Explicit. Just do it and your coding life will be a lot easier.

That being said. I think this line

Please Login or Register  to view this content.

means that if every cell in the range is "Disc." This is clearly not what you want.

So I came up with the code below. The basic logic is to check to see if the cell changed is in the range (H68:J74) - this is the intersect part of the code. If the changed cell is outside the range then the intersection is nothing, hence the code. We don't want to do anything if the changed cell is not in the range.

Then loop through the range to see if any of the cells are "Disc." If you find even one, set the flag to true. You can stop the loop here (Exit For) since any single occurrence is sufficient to hide the cells.

Then check the flag and if it was "tripped" to TRUE (meaning a "Disc" was found), hide the rows, otherwise each cell passed the test and the flag remained false, so un-hide the rows.

Please Login or Register  to view this content.