Hide/Unhide Rows based on Zero Value

**chiz** · 02-04-2010, 12:26 PM

Hello All,

I am graphing data compiled using sumproduct. I have the sumproduct =value linked to a dropdown, so if one value is chosen, all other suproduct rows are 0s except the value chosen.

This allows the chart to show one value, except the zeroed data still shows on the chart legend. It looks like the only way to remove the legend entries is to hide the zeroed rows.

Would you be able to show me a VBA that would hide/unhide rows based on whether a cell is zero?

Attached is a dummy sheet as an example. I would like to hide Rows based on values in column B. If the Value in B,n is 0 I would like to hide row n. These rows will change from 0 to other numbers and if the value in B,n is not 0, I would like to unhide that row.

Alternatively, is there an easier/more elegant way to do what i'm doing???

Thanks again for the help, you guys are great!

-chiz

**excelxx** · 02-04-2010, 03:44 PM

Hi

I think the code below may help you in hiding rows with zero value in column B and un-hiding the rows where the value is no longer zero.

Please Login or Register  to view this content.

**chiz** · 02-05-2010, 04:34 PM

Thanks excelxx, it works! There is one problem; it seems to skip over hidden rows, so once a row is hidden it is hidden forever. Any idea on how to make it check hidden rows as well? If not I will insert another macro to unhide all rows.

Thanks!!

**excelxx** · 02-06-2010, 09:19 AM

Hi chiz

The macro will unhide hidden rows where the value in the cell is not equal to zero. Did you want to unhide all hidden rows regardless of the value in column B.

excelxx

**Palmetto** · 02-06-2010, 10:48 AM

This will use Auto-Filter to hide/unhide rows based on changes in values in cells B8:B10 per your sample workbook.
It is much more efficient than looping.

Please Login or Register  to view this content.

**chiz** · 02-08-2010, 03:51 PM

Thanks Guys,

excelxx, I think what is happening is when the row is hidden, my sumproduct formula won't update.

Palmetto, thanks for the code, however when I try and use it, it brings up a macros box, asking which macro to use... what am I doing wrong?

**Palmetto** · 02-08-2010, 08:08 PM

Palmetto, thanks for the code, however when I try and use it, it brings up a macros box, asking which macro to use... what am I doing wrong?

The code I gave is based on your sample workbook and is designed to go into the sheet module and execute when a cell, in the range of B8:B9, changes value. It was not intended to be assigned to a button.

To get a precise solution from the start, you should always post a sample workbook that EXACTLY duplicates the structure of your real workbook. In order to avoid more questions, please upload per the aforementioned.

**excelxx** · 02-09-2010, 05:28 AM

Hi chiz

Are you trying to hide the rows with the series of months? If that's the case do you want to hide the rows when all the months add up to zero. Or are you hiding the rows when a single month is zero?

Thanks
excelxx