Automatically Hide rows with a value of zero

[tim]

I would like to get a VBA code to hide all rows for a given range that
have a value of zero in column z. Column z has an IF statement
formula. I would like to have the code activate automatically, so if
the value in column z changes to zero the row would be hidden or vice
versa. I have a macor that will run and hide them but I have to run
the macro - I'd like it to happen automatically.

Thanks,

 

[Guest]

Put the following in worksheet code:

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Set r = Intersect(Range("Z:Z"), ActiveSheet.UsedRange)
For Each rr In r
If rr.Value = 0 Then
rr.EntireRow.Hidden = True
End If
Next
Application.EnableEvents = True
End Sub

 

[Gord Dibben]

Might do better with this that checks for formula cells and hides just those
rows with 0 in Z and not all blank rows in used range.

Private Sub Worksheet_Calculate()
On Error GoTo stoppit
Application.EnableEvents = False
With ActiveSheet.UsedRange
.Rows.Hidden = False
For Each cell In .Columns(26).SpecialCells(xlCellTypeFormulas)
If cell.Text = "" Or cell.Value = 0 Then _
cell.EntireRow.Hidden = True
Next cell
End With
stoppit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP