Barrie....you stated "if you must use VBA".....can you think of another way?

Posted by JC on November 07, 2001 8:53 AM

here is my original post...

"I'm looking for a way to hide rows if certain criteria is met....for example if the value of a certain cell is zero, or blank. Any thoughts? I know this can't be done with conditional formatting, but maybe with some simple VBA...I'm not a VBA expert, so please explain in a bit of detail if you have any ideas...THANKS!!"

Let me add a few details...this is a department spending report (each dept. has it's own tab), so I can't sort the data...it needs to flow by account...I'm looking for a way to hide the rows that represent accounts where that department has not spent any $, or possibly where they have spent very little....(stinks we can't use conditional formatting to reference a single cell in a row)

I can't believe I need to use excel for this (so many inexpensive reporting applications to lay over any ERP), but unfortunately I have no choice....

I would really like to hear a few ideas on this....Mark W. you mentioned "Use AutoFilter (nt)", but there was no explanation....can you expand?

Finally let me say that I found this site recently and think it is GREAT! What an asset to be able to exchange ideas with others using this tool....I hope I can contribute as time goes on.

Thanks,
Jeff

Posted by Barrie Davidson on November 07, 2001 9:03 AM

Jeff, you can use VBA (as per my previous posting) or you can use auto filtering (as per Mark's suggestion). To use auto filtering, select the data set in the worksheet (Note that each column needs a header cell). Then, from the main menu, select Data|Filter|AutoFilter. This will put drop down arrows in each header (row 1). You can then specify criteria to filter on, including custom filter.

Play a bit with the auto filter (I think it will be better for you than using VBA) and if you need any help let me know via this board.

Regards,
BarrieBarrie Davidson

Posted by JC on November 07, 2001 9:20 AM

Re: Barrie....you stated

:: Barrie,

:: Doesn't auto filter place the headers on the columns, not the rows? This also isn't something I want to do on each sheet in each row(there are about 20 of them each with a series of 70+ rows(accounts))....let me know if my post is unclear....thanks again for your ideas!

Posted by Richard S on November 07, 2001 9:33 PM

Don't give up on autofilter

Jeff

Autofilter does place the headers on the columns. You want to hide specific rows I assume. If columnA has the account# and columnB has the balance, if you use autofilter, and click on the drop down arrow on columnB, and select greater than an amount you specify (you said you might want to exclude small amounts), it will hide all rows (accounts) that meet that criteria. If you have about 20 sheets, you could record a macro while you do this the first time, and then just run the Macro each month (or whenever).
Is this what you wanted?
Richard