Question for the Excel guru's out there

[GregH]

I know some of you have helped me in the past with excel questions I've had so I'm trying my luck again.

I have a spreadsheet listing a bunch of field production batteries that truck volumes into our facility.
There are around 70 different batteries that truck in, but in any given month there are usually only 10-20 of the 70 that actually deliver.
Since I don't really want to see the info if they didn't truck in I've been manually hiding the rows with a zero value (i.e. nothing trucked in)

Is there some way, via macro's or something else, that I can basically say: "If value = zero, then hide row x"

It's further complicated that there are two columns containing values for each battery, and sometimes one column has a zero but the other has a value, in which case I wouldn't want to hide that row.

So I guess what I'm really looking for is "If the values in columns A AND B for row x = zero, then hide row x.

So? Is this possible to do? Not a big deal if it isn't as I've just been hiding the rows manually for the last 6 years, but quicker ways to do things are always good :)

[grinder]

you can't toggle row's/columns's to be visible or not.

At best I would:

a) set your XLS file into two sheets. 1 as a back-end for raw data (batteries 1 thru 70), the other sheet that reports on said data using macros and what-not. (you can use Visual Basic inside this "dashboard" XL sheet to query the back-end raw data sheet and make it display much more cleanly).

b) dump excel and move to MS-Access. It's better at entering data, storing data, compiling data and reporting on data.

[PerryC]

Conditional formatting is what you are looking for. I am not sure you can use it to actually hide rows, but I do know you can use it to return zeros, or blank any cell that is empty.

Which version of excel are you using? I can give you more precise details once I know that.

[LarkStarr]

you could also sort the rows to have the zeroes on the bottom to "hide" them.

[GregH]

Using Excel 2010.
I'd consider using access but we don't have it here.

I've used conditional formatting elsewhere but there's no option to hide rows that I could see.
Sorting by value might be an idea...would certainly make it easier to hide them all together rather than line-by-line.

I haven't used macros much so was wondering if there would be a way to do it by using them.

[Squeetard]

Set the row height to 0 if cell A + cell B = 0. Else set row height to 12.

[GregH]

Originally Posted by Squeetard

Set the row height to 0 if cell A + cell B = 0. Else set row height to 12.

That'd work, but how exactly do I go about doing that?

[grinder]

good thinking squeetard, the row height works nice! Sample spreadsheet attached. GregH... you will need to change your excel options to display the developer ribbon so you can see the VB code.

http://www.caffeinatedinc.net/shared...ightmacro.xlsm

This should point you in the right direction

[GregH]

Originally Posted by grinder

good thinking squeetard, the row height works nice! Sample spreadsheet attached. GregH... you will need to change your excel options to display the developer ribbon so you can see the VB code.

http://www.caffeinatedinc.net/shared...ightmacro.xlsm

This should point you in the right direction

That nearly does what I need, but I'd still need to see the values in column C.

Looks very promising so far though - thanks a bunch!