Hide Zero Rows - But Not Blank Rows
 

Hello,

Need to hide rows that a certain range sums to zero in, which the code below
does.
What it also does is hide any blank rows as well, which does not work for
the intended purpose.

Tried numerous attempts at [paraphrasing] ....

If row = 0 hide AND If row = "" do not hide

.... To no avail.

Thanx in advance to anyone who can solve this.

- Mike


Sub HideRows()
ActiveWindow.DisplayZeros = True

On Error Resume Next
With Range("e13:i600")
..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

On Error Resume Next
With Range("e13:i600")
..EntireRow.Hidden = False
For i = 1 To .Rows.Count
If WorksheetFunction.Sum(.Rows(i)) = "" Then
..Rows(i).EntireRow.Hidden = False
End If
Next i
End With

End Sub

Hide Zero Rows - But Not Blank Rows
 

probably a better way to do this but adapting your code I think this may do
what you want:

Sub HideRows()
ActiveWindow.DisplayZeros = True
On Error Resume Next
Application.ScreenUpdating = False
With Range("e13:i600")
For i = 1 To .Rows.Count
If WorksheetFunction.CountA(.Rows(i)) 0 Then
If WorksheetFunction.Sum(.Rows(i)) = 0 Then
.Rows(i).EntireRow.Hidden = True
Else
.Rows(i).EntireRow.Hidden = False
End If
End If
Next i
End With
Application.ScreenUpdating = True
End Sub
--
JB


"MikeF" wrote:

Hello,

Need to hide rows that a certain range sums to zero in, which the code below
does.
What it also does is hide any blank rows as well, which does not work for
the intended purpose.

Tried numerous attempts at [paraphrasing] ....

If row = 0 hide AND If row = "" do not hide

... To no avail.

Thanx in advance to anyone who can solve this.

- Mike


Sub HideRows()
ActiveWindow.DisplayZeros = True

On Error Resume Next
With Range("e13:i600")
.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

On Error Resume Next
With Range("e13:i600")
.EntireRow.Hidden = False
For i = 1 To .Rows.Count
If WorksheetFunction.Sum(.Rows(i)) = "" Then
.Rows(i).EntireRow.Hidden = False
End If
Next i
End With

End Sub

Hide Zero Rows - But Not Blank Rows
 

John,

Thank you.
This worked.

But I think you're right, there MUST be a better way to achieve these results.

Although the sheet doesn't lend well to Advanced Filter, re non-consistent
field-names, shoudn't there still be a "standard" piece of code that says
"look at range nn:nn, and if the sum of x thru y cells = 0, hide the entire
row, and disregard any cells the sum to null" ??

But needed a quick fix, and you certainly came through.
Thanx again.

- Mike

"MikeF" wrote:

Hello,

Need to hide rows that a certain range sums to zero in, which the code below
does.
What it also does is hide any blank rows as well, which does not work for
the intended purpose.

Tried numerous attempts at [paraphrasing] ....

If row = 0 hide AND If row = "" do not hide

... To no avail.

Thanx in advance to anyone who can solve this.

- Mike


Sub HideRows()
ActiveWindow.DisplayZeros = True

On Error Resume Next
With Range("e13:i600")
.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

On Error Resume Next
With Range("e13:i600")
.EntireRow.Hidden = False
For i = 1 To .Rows.Count
If WorksheetFunction.Sum(.Rows(i)) = "" Then
.Rows(i).EntireRow.Hidden = False
End If
Next i
End With

End Sub

Hide Zero Rows - But Not Blank Rows
 

Can you tell me how to solve the same issue of not hiding the totally blank
rows but using the hiderows vbe as follows:

Sub Hiderows()
Dim lr As Long, i As Long
With ActiveSheet
lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lr
If Cells(i, 3) = 0 And Cells(i, 4) = 0 Then
Cells(i, 1).EntireRow.Hidden = True
End If
Next
End With
End Sub

I would be most appreciative!

Thanks!

Heidi

"MikeF" wrote:

Hello,

Need to hide rows that a certain range sums to zero in, which the code below
does.
What it also does is hide any blank rows as well, which does not work for
the intended purpose.

Tried numerous attempts at [paraphrasing] ....

If row = 0 hide AND If row = "" do not hide

... To no avail.

Thanx in advance to anyone who can solve this.

- Mike


Sub HideRows()
ActiveWindow.DisplayZeros = True

On Error Resume Next
With Range("e13:i600")
.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

On Error Resume Next
With Range("e13:i600")
.EntireRow.Hidden = False
For i = 1 To .Rows.Count
If WorksheetFunction.Sum(.Rows(i)) = "" Then
.Rows(i).EntireRow.Hidden = False
End If
Next i
End With

End Sub