How to write VB to hide/unhide row based on helpers value

[novice2430]

Please Login or Register  to view this content.

I'm using this code to hide or unhide rows based on a value in column A.
The code works as far as hiding a row, but it will not unhide a hidden row whos value in colum A changes from zero to any other number.
Maybe I'm not understanding this code right?

Can this event be triggered by an if function?

[JBeaucaire]

Try this:

Please Login or Register  to view this content.

[novice2430]

Thanks for the code JBeaucaire,

I assigned this event to a button, but it's becoming a pain to trigger. Is there any way I can write an if function in a helper column to trigger the event whenever a zero value shows?

[JBeaucaire]

Why would you assign a worksheet macro to a button? Worksheet macros activate themselves.

If you put the macro into a regular module, that was the error. Move it to the SHEET module where this is supposed to be active and it will trigger itself.

Right-click on the sheet name and select VIEW CODE.

[novice2430]

Ive placed it into the sheet module just like you explained and nothing happens. This happened before and this is why I created a button. When I assign it to a button it works whenever the putton is pressed. However, I can't get it to trigger itself.

[JBeaucaire]

Click on GO ADVANCED and use the paperclip icon to post up your workbook. We'll find the problem.

[novice2430]

I've attached an example

[JBeaucaire]

Let's just try changing it to a Worksheet_Change event. Typing a "0" into a cell doesn't seem to qualify as a "calculate the sheet" event, where as typing =1 does. Odd, huh?

Please Login or Register  to view this content.

[novice2430]

That is odd I'll try it out.

[novice2430]

That did the trick, thanks once again for the help!

[JBeaucaire]

Hah, I figured out a trick to getting the CALCULATE event to trigger all the time, I put a volatile formula in an empty cell which causes the sheet to recalculate even when I type 0 in a cell.

An example of a volatile function is INDIRECT(). So I just put =INDIRECT(A1) way off in AA1, and even though that results in an error, it still causes the worksheet_calculate to occur with every change.

Having done that, there's no reason not to just use the Worksheet_Change event.

[novice2430]

I hope the change event doesn't kill my memory. This will be used on a spreadsheet that is associated with dde, I have 20,000 rows total. I'll have to wait and see on Monday.
This may be a reason to buy a new Computer...

[JBeaucaire]

No, even on a fast computer, running a never-ending LOOP on 20,000 rows of data is bad idea. You can create a 20k row of data right there at home and see what I mean. You want to rethink this.

With THAT much data, I'd just turn on AUTOFILTER and trigger it manually whenever needed. Or put the MACRO into an on-demand macro.