how to Hide rows? if cells are blank?

[Steven]

just wondering how I would be able to hide rows on sheets if some cells are
blank.

I have a sheet set up to SUMIF values from about 16 other sheets, and it
returns the values depending on 72 corresponding codes, I would like to hide
any of the 72 rows of data that are blank.

the values returned will change on a week to week and daily basis, so I
would like to be able to use the same design for each new week, hope that
makes sense.

 

[Guest]

Try this

Select a column that has a value in it where if that cell is blank, you want to hide the row
Hit Edit-Go to-Special, and choose Blanks, OK
Hit Format-Rows, Hide

<-*-><-*-><-*-><-*-><-*-><-*-><-*-><-*-
Hope this helps
Anne Tro
Author: Dreamboat on Wor
Email: Dreamboat*at*Piersontech.co
Web: www.TheOfficeExperts.com

 

[Steven]

sorry my bad, the cells contain formulas they are only blank because `zero
values` in >tools>options>view is unchecked.

 

[Ken Wright]

So do as Anne suggested but filter on 'Not Equal to 0'

 

[Steven]

Still same porblem

So do as Anne suggested but filter on 'Not Equal to 0'

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

-------------------------------------------------------------------------- --
It's easier to beg forgiveness than ask permission
-------------------------------------------------------------------------- --

 

[Ken Wright]

OK, to keep things simple, how about just using a helper column that simply
returns Hide or Show depending on any number of scenarios you want to put in,
eg:-

=IF(OR(B2="",B2=0),"Hide","Show")

Then just filter on Show's. You can even format that column with a white font
of you don't like seeing it

 

[Ken Wright]

By the way, what version of Excel, only 2000/2002/2003 all filter out the blanks
using a filter of NonBlanks, even if returned by a formula.

 

[Steven]

cheers, colum A now has Hide or show, how do i use this to auto hide rows?

Thanks,
Steve

 

[Ken Wright]

Select that column and do Data / Filter / Autofilter, then click on the dropdown
arrow and choose Show

 

[Anne Troy]

You're the man, Ken.

 

[Ken Wright]

Hey Stranger, really really good to hear you again - Had me kinda worried there
you know

 

[Steven]

Autofilter is still not available, its greyed out, for now I im using sort
and manually hiding the rows

cheers for the help,

Steve

 

[Ken Wright]

Is the sheet protected or something??

 

[Steven]

nope and its not grouped.

Steve

Is the sheet protected or something??

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

-------------------------------------------------------------------------- --
It's easier to beg forgiveness than ask permission
-------------------------------------------------------------------------- --

Autofilter is still not available, its greyed out, for now I im using sort
and manually hiding the rows

cheers for the help,

Steve

You're the man, Ken.

Select that column and do Data / Filter / Autofilter, then click on the
dropdown
arrow and choose Show

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------------------------------------------------------------------------------

 

[Ken Wright]

OK, as per the other post:-

Either we are all talking by each other here, or something is screwy somewhere.
This is something that would usually be a 2 second job to most of the regulars,
so I know we're not all losing it. Are you able to send me the file at all so I
can take a look first hand, only we've pretty much covered most things it could
be??

ken.wright [at] ntlworld.com