AUtomatically hiding a row when a certain cell is blank

[kirbster1973]

Hi there,

I have a table which shows the languages spoken by children in our school.
In column B are all the possible languages, Column C shows the number of
chidlren who speak that particular language. WHen I break this down into
class, there are some rows which have a blank cell under the number of
chidlren who speak that language.

I would like to be able to automatically hide any row that has that
particular cell as a blank.

Is it possible?

[pikapika13]

choose a cell you would like to hide.
choose "conditional formatting" in the format menu.
choose "formula is"
type: =isblank(c3) [assuming you're in row 3]
choose white font (or the same color as your worksheet background).
press ok.

now to copy this format to the rest of the cells:
copy that cell you just applied this to.
highlight all the cells in that column you want to possibly hide.
Right click>>choose paste special>>choose format.

Hope this is what you're looking for.

[swatsp0p]

Another option is to use Auto-Filter on your range of data. On the drop down for the Number of Students, select Non-Blanks. This will hide all rows that have a blank cell in this column.

The benefit of this is that when you do get a student that speaks that language, you simple need to select (Blanks), enter your number in the appropriate row and then re-select (NonBlanks)

Would this work for you?

For detailed info and training on autofilter, type "all about auto filter" in the help box in Excel.

[kirbster1973]

Hi there, I'd thought of that but i'd want the whole table to reduce from the
26 rows down to maybe 9 or 10 which is typically the number of different
languages in a class with a total number of children at the bottom, Currently
I have a table with 26 languages which are spoken across the school, the
number of kids speaking each language is claculated by a SUM PRODUCT formula
relating to another sheet, therefore each time it finds a language it counts
it, if it doesn't find the language it leaves the cell blank, therefore at
the end I have maybe 15 or 16 languages showing but nothing in the "number"
cell. I ideally, would like to be able to shrink the number of cells by
hiding the blank cells but keep the layout of the table.

Hope this makes sense!!

"pikapika13" wrote:

>
> choose a cell you would like to hide.
> choose "conditional formatting" in the format menu.
> choose "formula is"
> type: =isblank(c3) [assuming you're in row 3]
> choose white font (or the same color as your worksheet background).
> press ok.
>
> now to copy this format to the rest of the cells:
> copy that cell you just applied this to.
> highlight all the cells in that column you want to possibly hide.
> Right click>>choose paste special>>choose format.
>
> Hope this is what you're looking for.
>
>
> --
> pikapika13
> ------------------------------------------------------------------------
> pikapika13's Profile: http://www.excelforum.com/member.php...o&userid=10892
> View this thread: http://www.excelforum.com/showthread...hreadid=375591
>
>

[kirbster1973]

Absolutely perfect thankyou, just recorded a macro and assigned a button to
do exactly that.

Thanks alot

"swatsp0p" wrote:

>
> Another option is to use Auto-Filter on your range of data. On the drop
> down for the Number of Students, select Non-Blanks. This will hide all
> rows that have a blank cell in this column.
>
> The benefit of this is that when you do get a student that speaks that
> language, you simple need to select (Blanks), enter your number in the
> appropriate row and then re-select (NonBlanks)
>
> Would this work for you?
> >
> > For detailed info and training on autofilter, type "all about auto
> > filter" in the help box in Excel.
>
>
> --
> swatsp0p
>
>
> ------------------------------------------------------------------------
> swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
> View this thread: http://www.excelforum.com/showthread...hreadid=375591
>
>

[swatsp0p]

I am glad this worked for you. Thanks for the feedback, it is always appreciated.

Cheers!

Bruce