Delete rows if fits criteria in a Macro

[forest8]

Hi

I currently have a macro I'm working on in which there are 12 worksheets
(January to December).

In each spreadsheet I want to delete all rows that contain "0" in a
particular column but keep all the others.

This is the current code I'm using:

Columns("E:E").Select
Selection.NumberFormat = "0"

For Each c In Range("E1:E200")
If c = 0 Then Rows(c.Row).Delete
Next c

In the 12 worksheets, it deletes all but 1 or 2 of the rows I want gone.

How do I fix it so that all the rows are deleted?

Thanks in advance for your help.
Forest

 

[Mike H]

Hi,

If you work forward through a range deleting as you go then if 2
consecutive rows match your criterial you'll miss the second. Try this which
loops through all your worksheets and block deletes those rows that meet your
criteria

Sub sonic()
Dim MyRange1 As Range
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
For Each c In ws.Range("E1:E200")
If c.Value <> "" And c.Value = 0 Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next c
If Not MyRange1 Is Nothing Then
MyRange1.Delete
End If
Set MyRange1 = Nothing
Next ws
End Sub

Mike

 

[JLGWhiz]

When deleting rows, you should start at the bottom of the column and work
upward. This avoids the row skips that occur when deleting from top down,
due to the default shift up after deletion.

Change this:
For Each c In Range("E1:E200")
If c = 0 Then Rows(c.Row).Delete
Next c

To:
For i = 200 To 1 Step -1
If Range("E" & i) = 0 Then
Rows(i).Delete
End If
Next

 

[forest8]

Hi

The second solution was very helpful for me. Unfortunately, the first
solution did not solve my issue.

Thanks for the help.
J