Results 1 to 7 of 7
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete line if...SUMM IS=0 (2000 sr 1)

    This sheet contain value of aumont in column I
    In column L a index in column D other one.
    How to delete line if in internal of 2 index the agebric summ is =0 delete the line...
    Example:
    line 9 and 10 have the same index in L and D and two value are equal to 0 (-46.170,00 and 46.170,00) in this case delete the 2 lines

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

    Re: Delete line if...SUMM IS=0 (2000 sr 1)

    Try this:
    <code>
    Sub DeleteZeroSum()
    Dim r As Long
    Dim s As Long
    Application.ScreenUpdating = False
    For r = Range("D65536").End(xlUp).Row To 4 Step -1
    For s = r - 1 To 3 Step -1
    If Range("D" & r) = Range("D" & s) And _
    Range("L" & r) = Range("L" & s) And _
    Range("I" & r) + Range("I" & s) = 0 Then
    Range("A" & r).EntireRow.Delete
    Range("A" & s).EntireRow.Delete
    r = r - 1
    End If
    Next s
    Next r
    Application.ScreenUpdating = True
    End Sub
    </code>
    Note: this code only looks at pairs of rows, not at the sum of all rows with the same index in D and L.

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete line if...SUMM IS=0 (2000 sr 1)

    EXCELLENT!!!
    But i have see in mny code you use (...4 Step -1)
    only to undestand tell me why...
    Tks.

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

    Re: Delete line if...SUMM IS=0 (2000 sr 1)

    In the line

    For r = Range("D65536").End(xlUp).Row To 4 Step -1

    Step -1 means that the code loops backwards, from the last filled row in column up to row 4. When deleting rows it is better to loop backwards.

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete line if...SUMM IS=0 (2000 sr 1)

    AHHHHHHHHHHHHHHHHH....nice!
    but this idea not is for me....

    Remember (about 3 year ago...):
    Sal(cell0,0) Hans(cell99999999999999,999999999999999)
    I am sure you remember this;-)

  6. #6
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete line if...SUMM IS=0 (2000 sr 1)

    Hans i am very sorry...
    But my application from CICS devide in two part the amount... (+ and -)
    I attach new wbook and new scenario...
    Based the old note is possible to up date your code?
    Tks and sorry.

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

    Re: Delete line if...SUMM IS=0 (2000 sr 1)

    Please explain what you want.

    Added later: simply place the data below each other in one sheet, then run the code I posted earlier.

Posting Permissions

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