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?

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 clickchoose paste specialchoose 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

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 clickchoose paste specialchoose 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

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

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

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

Cheers!

Bruce


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=375591

I have used the auto filter and it works brilliantly by selecting NonBlanks,
but the problem I'm having is that the data in the cells is not typed into
those cells, but rather I'm using vlookup to enter an address from a table on
another sheet, depending on what number (address ID) is entered elsewhere.
Some addresses are made up of more lines than others, meaning that some
addresses have blank lines in them, which is what I want to hide. If I select
the appropriate address and then autofilter, selecting NonBlanks the blank
lines are hidden as it should be. HOWEVER if I enter a different address ID
number, then vlookup looks up the different address, but the autofilter
doesn't AUTOMATICALLY select the NonBlanks from this new address! Shouldn't
it automatically do this? (I am doing this in a template - don't know if this
makes any difference?)


"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