Sumif and zero values question

[Gordon]

I have a long list of product codes. Against each code is a number obtained
from a sumif calculation from another long list of the same codes. A large
number of these sumif results are zero. Is there a way of automatically
hiding the zero lines such that if one became greater than zero it would
automatically appear in the list?
In other words I would like to be able to automatically only list those
product codes where the sumif result was greater than zero.

(Hope that's clear!)

Thanks

 

[Gordon]

I have a long list of product codes. Against each code is a number obtained
from a sumif calculation from another long list of the same codes. A large
number of these sumif results are zero. Is there a way of automatically
hiding the zero lines such that if one became greater than zero it would
automatically appear in the list?
In other words I would like to be able to automatically only list those
product codes where the sumif result was greater than zero.

(Hope that's clear!)

Thanks

Sorry, meant to add Excel 2002.

 

[Freemini]

One way is to format your cells using the custom number format
0;-0;

This will show positive numbers, negative numbers with a leading - and
blank for zero.

hth

Mike

 

[Gordon]

One way is to format your cells using the custom number format
0;-0;

This will show positive numbers, negative numbers with a leading - and
blank for zero.

hth

Mike

I obviously was as clear as mud! It's not a question of the cell format,
what I'm after is some method of hiding completely the lines that return
zero, so that although the original list contains lines with a zero result,
the only lines that are visible are ones with a result greater than zero,
with no spaces between them. Basically what I'm after is like a database
query result, but permanently on, if that makes sense!

 

[Calligra]

If the value is on a row all by itself, then add code to hide the row i
the value is 0.

if sheet1.cells(1,1).value = 0 then
sheet1.rows("1").hide
end if

If the value is not all by itself, the code could get considerably mor
complicated. You cannot hide the cell, however, you can move th
contents to the bottom of the page and then hide the rows. Then othe
codes would need to be written to re-move the cell values and re-inser
into the appropriate place. Hope this information helps you

 

[Gordon]

If the value is on a row all by itself, then add code to hide the row if
the value is 0.

if sheet1.cells(1,1).value = 0 then
sheet1.rows("1").hide
end if

If the value is not all by itself, the code could get considerably more
complicated. You cannot hide the cell, however, you can move the
contents to the bottom of the page and then hide the rows. Then other
codes would need to be written to re-move the cell values and re-insert
into the appropriate place. Hope this information helps you.

Thanks - yes each value is on a separate row, but now the silly question -
where do I "add the code"?

Ta!

 

[Calligra]

You would either add it on the worksheet (goto F11 to open VB and the
double click on the worksheet that you are attempting to hide the row
and then determine if you want the code to active on change, on shee
deactivation or what... and then add the below code

 

[Gordon]

You would either add it on the worksheet (goto F11 to open VB and then
double click on the worksheet that you are attempting to hide the row)
and then determine if you want the code to active on change, on sheet
deactivation or what... and then add the below code.

Thanks for the Info - unfortunately in Excel 2002, F11 seems to open a
chart, not VB!

 

[Gordon]

Thanks for the Info - unfortunately in Excel 2002, F11 seems to open a
chart, not VB!

Got it! It's ALT-F11 !