Hide Rows if the Value is ZERO

[jammer6_9]

I am trying to hide a row if the value is zero in "Worksheet_Change Event" with this code but my code below doesn't work.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = True

Dim lastrow As Long, r As Long
lastrow = ActiveSheet.UsedRange.Rows.Count
For r = lastrow To 2 Step -1
If UCase(Cells(r, 1).Value) = 0 Then
Cells(r, 1).Select
Selection.EntireRow.Hidden = True


ElseIf UCase(Cells(r, 1).Value) <> 0 Then
Cells(r, 1).Select
Selection.EntireRow.Hidden = False


End If

Next r


End Sub


In addition how can I exclude the hidden row in my calculation using "SUM" formula?

[Bob Phillips]

The problem seems to me to be your use of UsedRange.Rows.Count. This will give you the number of rows in the used range, but that does not necessarily mean it begins at 1. It could be rows 10-15, which is 6 rows, but if you then loop from that number of rows to 2 backwards, you would go through rows 6 to 2, thereby completely missing teh UsedRange.

You could should adjust to the used range like so



For r = Me.UsedRange.Row + lastrow To Me.UsedRange.Row Step -1


But quite honestly, that is all pointless, you only need to hide it when it changes to 0



Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 And Target.Value = 0 Then
Target.EntireRow.Hidden = True
End If

End Sub