Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

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

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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    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
  •