How to Hide Row when 2 Columns have Zero as Values

[LaneFowler]

Hi: I need to hide an entire row if cell values in two non-adjacent columns are zero.

Worksheet has data in Columns A through L

Columns E and L contain dollar figures in rows 52 through 77. The cells are pre-loaded with zero dollars with anticipation that some of the zeros will be changed by the user.

If BOTH the values in Column E & L remain as Zero, I want to Hide the entire row. The Macro below stops working after it looks at the first row and will not continue to loop.

Any ideas? I think my If then statement is wrong???

Sub HideRows()
With Range("E52:E57", "L52:L57")
.EntireRow.Hidden = False
For i = 1 To .Rows.Count
If WorksheetFunction.Sum(.Rows(i)) = 0 Then
.Rows(i).EntireRow.Hidden = True
End If
Next i
End With
End Sub

Note that if I were only looking at Column E without a need to column L, the above macro will work with a change to the Range at ("E52:E77"), but when I ask Excel to analyze Two Columns, E & L, or two values in the same row, I have the issue.

Help is appreciated
<!-- / message -->

 

[tc200505]

The code that has been posted by you seems to be working fine. The only discrepancy that I could find is that your description specifies rows 52 through 77, whereas the code specifies a range from 52 to 57. Is this creating an issue?

 

[Domenic]

Try...

Option Explicit

Sub HidRows()

    Dim i As Long
    
    For i = 52 To 57
        Rows(i).Hidden = (Cells(i, "E") = 0) * (Cells(i, "L") = 0)
    Next i
    
End Sub

 

[LaneFowler]

Thank You! I meant to type my post with range to rows 77 (not 57). My macro was input through row 77, but still did not work.

Your Code DID Work, and it is Much Shorter. Thank you VERY MUCH!!!

One more question on this code and scenario. As staed, Columns E and L contain dollar figures in rows 52 through 77. The cells are pre-loaded with zero dollars with anticipation that some of the zeros will be changed by the user. Your code works exactly as stated: if the values in column E and Column L are zero, the entire row is, indeed hidden.

Part 2 of the question is as follows:

There are 3 header rows on top of row 52 (Rows 49 through 51) that specify the contents of the rows below, rows 52 through row 77. If the values in E52:E77 and L52:L77 remain as zero, I would also like rows 49 through 51 to be additionally hidden. However, if any value in the cells E52:E77 & L52:L77 is not zero, I want the headers in the 3 rows to remain visible. The code I added below in red is very very wrong and does not work.

Sub HidRows()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Dim i As Long<o></o>
<o></o>
For i = 52 To 57<o></o>
Rows(i).Hidden = (Cells(i, "E") = 0) * (Cells(i, "L") = 0)<o></o>
Next i
If Range("E52:E77", "L52:L77") = 0 Then<o></o>
Rows("49:51").hidden = True<o></o>
Else<o></o>
Rows("49:51").hidden = False<o></o>
End If<o></o>
End Sub

Can I ask for one more helping hand?

 

[Domenic]

Try...

If Application.Subtotal(109, Range("E52:E77, L52:L77")) = 0 Then
    Rows("49:51").Hidden = True
Else
    Rows("49:51").Hidden = False
End If

 

[LaneFowler]

Thanks Domenic. The second part of your provided macro also works in regard to hiding the headers above when all values within the 2 columns from the specified range are zero. Much appreciated.

One more question: can you explain why the maco includes the command in regard to: "Option Explicit"

 

[Domenic]

Thanks Domenic.

You're very welcome!

One more question: can you explain why the maco includes the command in regard to: "Option Explicit"

It forces one to declare all variables. As per the help file...

"If you don't use the Option Explicit statement, all undeclared variables are of Variant type unless the default type is otherwise specified with a Deftype statement."

"Use Option Explicit to avoid incorrectly typing the name of an existing variable or to avoid confusion in code where the scope of the variable is not clear."