Macro to delete rows with criteria of "0"

[Emb21]

Hello all,

Still on Excel 2003

My macro is only deleting some of the rows and I dont know why. Note the I have the formating set to currency in M9:M44.

Sub Rectangle7_Click()
ActiveSheet.Unprotect
Dim x As Range
Dim y As Range
Set y = Range("M29:M44")
Application.ScreenUpdating = False

For Each x In y
If x.Value = "0" Then
x.EntireRow.Delete
End If
Next x

ActiveSheet.Protect
End Sub

Any help would be appreciated.

Thanks,
Eric from PA

 

[jeffreybrown]

Hi Eric from PA,

Try removing the "" from around the 0.

 

[Emb21]

Thanks Jeff but still not working :/

 

[AlphaFrog]

Say you delete row 5. Then row 6 will move up to replace it. Then your macro will test if row 6 is zero, but what was row 6 is now row 5, and what was row 7 is now row 6. So when your macro now tests if row 6 is zero, it skips over old row 6 (which is now row 5 because it moved up) and really tests old row 7 (which is now row 6).

You need to test each row from the bottom up.

Sub Rectangle7_Click()
    
    Dim x As Long
    
    ActiveSheet.Unprotect
    Application.ScreenUpdating = False
    
    For x = 44 To 29 Step -1
        If Range("M" & x).Value = 0 Then Rows(x).Delete
    Next x
    
    Application.ScreenUpdating = True
    ActiveSheet.Protect
    
End Sub

 

[Emb21]

Thanks Alpha Frog it worked!