expand and collapse across multiple sheets with one control

[waternut]

Hi,

I have a workbook with ~15 sheets (and growing) in it, all of a varying number or rows. In column A I could always put an IF statement that if there is data in a particular cell, return "filled" and then manually filter to only show the "filled" rows, however I was wondering if there is a way to have one "toggle" (if that is the correct word) on one master sheet that would collapse all those empty rows? I have attached a simplistic example where I would like to automate the filtering in cell A1. Not sure if a macro is the tool, or if there is another solution. I have only begun to explore macros so my knowledge is very limited.

Any assistance is greatly appreciated.

Thanks,

Chris

[TMS]

Copy the following code into a standard module. Add a button on the MASTER sheet and assign the macro to it.

Please Login or Register  to view this content.

Regards, TMS

[waternut]

TMS, thank you very much, that worked perfectly!!!!!

[TMS]

You're welcome.


Note that this routine uses actual blank cells in column B rather than the text "FILLED" in column A. From that perspective, column A is redundant. You could also do something similar by switching on and off the AutoFilter on column B. Again, no need for column A.

Please Login or Register  to view this content.

Regards, TMS

[waternut]

Hi,

Now that I have tried to implement this, I am having a run time error '1004': No cells were found. Which I think is coming up because I had to end up creating a formula in the column being evaluated. The formula is IF((COUNTIFS(I4:J4,">0")+COUNTIFS(I4:J4,"<0"))=0,"","X")which I need because I have some rows where the values negate each other, however I still want them to be displayed, such as $10 and -$10. Now that I have this formula in there, is there a way the original macro can be modified to account for the formula? TMS, in your last post I understand what you mean regarding the "FILLED" text in column A. I only did that in the original example to demonstrate what I has hoping I could get a macro to do for me.

I tried implementing the second macro, however I am not sure how to use it. After putting it into a module do I utilize the auto filtering on one sheet to expand and collapse the rows on every sheet, or do I need something else to make it work. All options and thoughts are appreciated.


Thanks,

Chris

[waternut]

BUMP to the top

[TMS]

Please Login or Register  to view this content.

Regards, TMS

[waternut]

A couple last questions, how would l modify this if instead of excluding the sheet named "MASTER" I wanted it to include only certain sheets, lets call them "SHEETS1" "SHEETS2" "SHEETS3" ?

Also in the above the macro appears to be keying off of a formula in column A to expand and collapse the rows, how would I alter it if the formula was say in row "F"?

Lastly what does

Please Login or Register  to view this content.

do as part of the macro? sorry for the newbie questions.

Chris

[TMS]

You're welcome. Thanks for the rep.

Point of note for future reference: it's always best to ask the question you want answering, not a hypothetical question with a sample workbook that doesn't reflect the live environment.

To be selective about the sheets, use

Please Login or Register  to view this content.

To filter on column F, use:

Please Login or Register  to view this content.

The code:

Please Login or Register  to view this content.

just determines the last row of data. It's equivalent to selecting the last/bottom row on the spreadsheet and pressing End followed by Up-arrow and looking at the row of the cell selected. In this instance it is based on column B as that is the column that is populated with a formula.

The revised code would look like this:

Please Login or Register  to view this content.

Regards, TMS

[waternut]

Hi TMS,

Thanks for all the help and my apologies for having to modify things as I went along. Typically I can post an example data set that replicates what I am after. In this case, it changed on me after the original posting, and I am unfortunately limited to these example files, as some of my data sets are sensitive and not available to be posted publicly. All that said I really appreciate your insight assistance and learning about macros.

Thanks.

Chris

[TMS]

You're welcome.