Hide entire row only when all values in noncalculated cells are zero

[nyt]

Hello all,

Unfortunately, I don't have any sound experience in VBA and I desperately need some assistance.

Here's some background -
Our PeopleSoft users run nVision reports using a layout (Excel file with .xnv extension) and would like a macro created to hide an entire row if all cells in the row are zero. This macro should then be added to an instance hook so it can be triggered when the report is run. Also, there are formulas in some cells, therefore I'm guessing that the code should have to look at non-contiguous cells.

Here is some code that I tried initially. The problem is that it hid the entire row even when some cells were greater than zero.

Sub HideRows()
Dim cell As Range
For Each cell In Range("G10:AB10")
If Not isEmpty(cell) Then
If cell.Value = 0 Then
cell.EntireRow.Hidden = True
End If
End If
Next
End Sub

Another challenge is that for some months there may be more data rows than what the layout displays, if that makes any sense. For example, the total row of the layout is on row 68. However, when I ran the report for June 2015, the report had 98 rows.

I have attached the layout file for your reference; however, the website would not allow me to add it in .xnv format, so I added it as an Excel file.

Thanking you in advance for your guidance and assistance.

Best regards,
Simone

[p24leclerc]

try the following code:

Please Login or Register  to view this content.

From you own code, I assumed you were interested only in columns G to AB.
If not, you can change it in the code to scan the columns you need to.

[nyt]

Thank you, p24leclerc. I will give this a try!