# Thread: Macro - delete rows with zeros (Excel 2002)

1. ## Macro - delete rows with zeros (Excel 2002)

Hi,
I have the following macro to delete data if the amount column (E) is zero. It kind of works, however it only deletes "some" of the rows that have zeros. I can't figure out why it isn't working. Any ideas?
Thanks!!
Lana

Sub testing()
Dim x As Long

x = 2

Do While Sheet3.Range("A" & x) <> ""
If Range("E" & x) = 0 Then
Range("A" & x & ":" & "G" & x).Delete
End If
x = x + 1
Loop

End Sub

2. ## Re: Macro - delete rows with zeros (Excel 2002)

You have to loop backwards.

For x = Sheet3.Range("A65536").End(xlUp).Row To 2 Step -1
If Range("E" & x) = 0 Then
Range("A" & x & ":" & "G" & x).Delete
End If
Next x

3. ## Re: Macro - delete rows with zeros (Excel 2002)

Of course that worked... why does it have to loop backwards?
Thanks Hans!
Lana

4. ## Re: Macro - delete rows with zeros (Excel 2002)

Let's take an example: rows 4 and 5 contain 0 in column E.

When looping forwards, the code detects that E4 = 0 when x = 4. It then deletes A4:G4.
In the next round, x = 5 and the code doesn't detect the 0 because the cell that was E5 before has shifted up to E4 because of the delete!

When looping backwards, the code first arrives at x = 5. It detects that E5 = 0 and deletes A5:G5.
In the next round, x = 4 and the code *does* detect the 0 in E4 because nothing has happened to E4 yet.

5. ## Re: Macro - delete rows with zeros (Excel 2002)

This makes perfect sense... thanks so much for the explanation Hans!!
Lana

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•