Hide rows that total $0

[tmleafs]

I would like to hide entire rows of information if they total $0.

Is their a way I can do this without using the filter option and hitting reapply all the time?

 

[texasalynn]

how would it be determined they total $0?

 

[VoG]

Maybe like this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
Target.EntireRow.Hidden = WorksheetFunction.Sum(Target.EntireRow) = 0
End Sub

 

[tmleafs]

It would be determined by the companies amount of work that they have done with us. If they haven't worked with us then Q1 - Q4 will equal $0.

 

[tmleafs]

<TABLE style="WIDTH: 340pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=450 border=0><COLGROUP><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3291" span=4 width=90><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3291" width=90><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl67 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 68pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=90 height=18>Q1 2010</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=90>Q2 2010</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=90>Q3 2010</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=90>Q4 2010</TD><TD class=xl75 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #d8d8d8" width=90>TOTAL 2010</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>$0 </TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>$0 </TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>0</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>0</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #d8d8d8" align=right>0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>$5,864 </TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>$10,308 </TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>$687 </TD><TD class=xl74 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>$0 </TD><TD class=xl77 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #d8d8d8" align=right>$16,859 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>$97,773 </TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>$309,422 </TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>$68,974 </TD><TD class=xl74 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>$13,847 </TD><TD class=xl77 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #d8d8d8" align=right>$490,016 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>$26,700 </TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>$0 </TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>$48,950 </TD><TD class=xl74 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>$56,740 </TD><TD class=xl77 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #d8d8d8" align=right>$132,391 </TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>$0 </TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>$0 </TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>$181,781 </TD><TD class=xl74 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>$30,147 </TD><TD class=xl77 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #d8d8d8" align=right>$211,927 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>0</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>$0 </TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>$0 </TD><TD class=xl74 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>$0 </TD><TD class=xl77 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #d8d8d8" align=right>0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>$34,571 </TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>$0 </TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>$145,638 </TD><TD class=xl74 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>$34,992 </TD><TD class=xl77 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #d8d8d8" align=right>$215,201 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>$0 </TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>$0 </TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>$0 </TD><TD class=xl74 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>$0 </TD><TD class=xl77 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #d8d8d8" align=right>$0 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>$59,112 </TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>$0 </TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>$25,437 </TD><TD class=xl74 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>$0 </TD><TD class=xl77 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #d8d8d8" align=right>$84,549 </TD></TR></TBODY></TABLE>

So, if the total 2010 number equals $0 then it would hide the entire row.

 

[tmleafs]

Anyone have an idea?

 

[VoG]

Assuming that the totals are in column E try

Sub Hide0()
Dim LR As Long, i As Long
LR = Range("E" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For i = 2 To LR
    Rows(i).Hidden = Range("E" & i).Value = 0
Next i
Application.ScreenUpdating = True
End Sub

 

[tmleafs]

Alright, is there a way if a cell becomes something other then $0 that it will reapear?

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Calculate()
Dim LR As Long, i As Long
LR = Range("E" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For i = 2 To LR
    Rows(i).Hidden = Range("E" & i).Value = 0
Next i
Application.ScreenUpdating = True
End Sub