Hiding rows which equal zero sum value
 

I am having difficulty hiding rows with a zero sum value because I have a
break in the sequence. In other words, I'd like to hide the following rows
C8:C88 and C106: C180, but because there is a break in the sequence I am not
sure how to re-write the below Macro. Any assistance would be appreciated.

Sub HideZero()
On Error Resume Next
With Range("D25:D84")
..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

Hiding rows which equal zero sum value
 

Hi Mike,

Here is one way to handle the problem:
Create a dummy column that devides 1 by the average of the row. Hide this
column or not.

Then use the following macro to hide all the rows with a sum of 0"

Sub HideZero()
[J1:J180].SpecialCells(xlCellTypeFormulas,16).EntireRow.Hid den=True
End Sub

In this example J is the column with formulas of the form =1/AVERAGE(A1:I1)
You could also add and remove this dummy column within the macro:
[J1:J180] = "=AVERAGE(RC[-8]:RC[-1])"

--
Cheers,
Shane Devenshire


"Mike" wrote:

I am having difficulty hiding rows with a zero sum value because I have a
break in the sequence. In other words, I'd like to hide the following rows
C8:C88 and C106: C180, but because there is a break in the sequence I am not
sure how to re-write the below Macro. Any assistance would be appreciated.

Sub HideZero()
On Error Resume Next
With Range("D25:D84")
.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

Hiding rows which equal zero sum value
 

This was helpful, thank you Shane!

"Mike" wrote:

I am having difficulty hiding rows with a zero sum value because I have a
break in the sequence. In other words, I'd like to hide the following rows
C8:C88 and C106: C180, but because there is a break in the sequence I am not
sure how to re-write the below Macro. Any assistance would be appreciated.

Sub HideZero()
On Error Resume Next
With Range("D25:D84")
.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

Hiding rows which equal zero sum value
 

Your very welcome
--

Shane Devenshire


"Mike" wrote:

This was helpful, thank you Shane!

"Mike" wrote:

I am having difficulty hiding rows with a zero sum value because I have a
break in the sequence. In other words, I'd like to hide the following rows
C8:C88 and C106: C180, but because there is a break in the sequence I am not
sure how to re-write the below Macro. Any assistance would be appreciated.

Sub HideZero()
On Error Resume Next
With Range("D25:D84")
.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