How to Hide Row when 2 Columns have Zero as Values

LaneFowler

New Member
Joined
Jul 14, 2010
Messages
30
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 -->
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

tc200505

New Member
Joined
Mar 7, 2010
Messages
28
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,244
Try...

Code:
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

New Member
Joined
Jul 14, 2010
Messages
30
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-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
Dim i As Long<o:p></o:p>
<o:p></o:p>
For i = 52 To 57<o:p></o:p>
Rows(i).Hidden = (Cells(i, "E") = 0) * (Cells(i, "L") = 0)<o:p></o:p>
Next i
If Range("E52:E77", "L52:L77") = 0 Then<o:p></o:p>
Rows("49:51").hidden = True<o:p></o:p>
Else<o:p></o:p>
Rows("49:51").hidden = False<o:p></o:p>
End If<o:p></o:p>
End Sub

Can I ask for one more helping hand?
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,244

ADVERTISEMENT

Try...

Code:
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

New Member
Joined
Jul 14, 2010
Messages
30
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,244
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."
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,730
Messages
5,513,063
Members
408,935
Latest member
Jmtramos

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top