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

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

"forest8" wrote:

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

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" wrote:

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

Hi

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

Thanks for the help.
J

"JLGWhiz" wrote:

> 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" wrote:
>
> > 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
> >