Toggle Button for Hiding/Unhiding rows

**drean03** · 03-07-2010, 06:55 AM

Hello Guys,
Need your help again.

I am trying to figure out how to hide rows automatically when 1 row or more rows have zero (0) values and/or blank.
Have attached here a working file for reference.

What I'm doing now is entering data in Sheet1(a) and/or Sheet2(a). Data here are linked to the printed sheet-Sheet1 and Sheet2.
Every month I will be printing this printed sheets numbering 10 or more and I will be adding more sheets to be printed in the future.
Before printing, I have to hide row/s having all 0 values across calculated, for all the 10 or more printed sheets. (e.g. In Sheet1, rows 10, 16, 19, 23 and 37). So this takes time.
Note that the columns in the printed sheet will probably remain the same.

For this, is it possible as well for a toggle button to be utilized for all the codes in the printed sheets to run-a. hide the rows with 0s when toggle on and unhide the rows when toggle off. Likewise, the caption will change between toggle.

This toggle button is placed in a summary sheet, but for this purpose, is placed in 1 - worksheet.

Can you guys help me code this?

Thanks in advance for the great help.

**6StringJazzer** · 03-07-2010, 09:26 AM

This works and is fast. Let me know if you have questions about how the code works. See attached.

Please Login or Register  to view this content.

**6StringJazzer** · 03-07-2010, 09:28 AM

Originally Posted by drean03

I am trying to figure out how to hide rows automatically when 1 row or more rows have zero (0) values and/or blank.

Not sure what you mean by that last part. My solution leaves blank lines alone since it looks like you deliberately left blank lines at the top of the sheets, and for spacing between sections.

**drean03** · 03-08-2010, 12:18 AM

Originally Posted by 6StringJazzer

Not sure what you mean by that last part. My solution leaves blank lines alone since it looks like you deliberately left blank lines at the top of the sheets, and for spacing between sections.

Right 6StringJazzer. Please disregard that one.

Just arrived in the office. I'll try the codes you provided and get back here.

Thank you.

**drean03** · 03-08-2010, 03:21 AM

Hello 6StringJazzer. Thanks, it worked really fine here-in this working file.

Now, I copied the worksheet '1' entirely to my original excel file (at the left-most tab). It worked fine for the first few sheets (summary worksheet data), but not in the Printed sheet. This file is composed of around 130+ worksheets, but only 50+ sheets will be printed. Some sheets have charts in it, some are just list of items, while some are summary of the printed sheets.

There is no problem if all worksheets will be affected by this code as long as the printed sheets will be previewed and printed right. I can unhide again anyway the rows for me to enter data in the source worksheets. (e.g. in sheet1(A) or sheet2(A))

But, do you think that it is better for only the Printed sheets will get affected? I mean the toggle button will only trigger to hide/unhide sheets with an event code. (e.g.Sheet1 and Sheet2 only, not the Sheet1(A) and Sheet2(A)). And further, this event code can be copied to future inserted worksheet/s.

Thanks a tons!

**6StringJazzer** · 03-08-2010, 10:46 AM

Originally Posted by drean03

But, do you think that it is better for only the Printed sheets will get affected? I mean the toggle button will only trigger to hide/unhide sheets with an event code. (e.g.Sheet1 and Sheet2 only, not the Sheet1(A) and Sheet2(A)). And further, this event code can be copied to future inserted worksheet/s.

I'm not exactly sure what you mean, but yes, the code can be modified to affect only certain sheets. I wrote it to affect all sheets. We can add some sort of indicator in the sheets to be affected that would be checked, or can specify explicitly which sheets would be affected. If there is a naming convention that easily identifies the sheets to be printed, that would be easiest. I would be happy to look at your full workbook if you would like (maybe too big to attach but you could email it to me, though not sure if that would be sanctioned by the mods).

**JBeaucaire** · 03-08-2010, 02:46 PM

Tip: If you can NAME your sheets in a way that indicate which should be printed, that would help in the discerning of which to apply the HIDE macro to. This would make it easier than maintaining code changes or a manual list somewhere.

So, maybe add a unique character to the sheetname that his is to be allowed. Sheet1, Sheet2, Sheet3...and you only want it to affect sheets 1 and 2, perhaps name them:

Sheet1#, Sheet2#, Sheet3

Then use this test in your macro:

Please Login or Register  to view this content.

========
Another way might be to include a key cell on each sheet. If AA1 = "hide" then the macro runs on that sheet...not as elegant and easier to break if columns get deleted or such, but it's an option.

Please Login or Register  to view this content.

**drean03** · 03-09-2010, 12:41 AM

Hello again 6StringJazzer and Jbeaucaire,

Sorry coz the file is for internal purpose. If I own it personally, I'd be glad to send or upload it here.

Anyway, what I mean is some unique identification code inside those sheets that will be printed to be called upon when the toggle button is pressed. That's the ideal option I have in mind.

Furthermore, when the need to add another sheet to be printed, I will just copy a Printed sheet with the code in it and this new sheet will have its row hid/unhidden when the toggle is pressed.

On the other hand, JBs suggestion's are fine with me, putting the # sign in the sheet tab or a key cell, if you think that's not possible.

Thanks again!

**teylyn** · 03-09-2010, 07:42 AM

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

Drean, you seem to have posted the same question again. You can continue in your other thread.

Thread Closed.