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,

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

--
Gary''s Student - gsnu200731


"(E-Mail Removed)" wrote:

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

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

On Wed, 20 Jun 2007 10:14:01 -0700, Gary''s Student
<(E-Mail Removed)> wrote:

>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