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

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

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
20,211
Office Version
  1. 365
Platform
  1. Windows
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
20,211
Office Version
  1. 365
Platform
  1. Windows

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
20,211
Office Version
  1. 365
Platform
  1. Windows
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."
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,398
Messages
5,831,407
Members
430,065
Latest member
ctornabe

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top