Macro to delete all rows that sum to zero

[howard]

I have a macro below to delete all rows there the debits and credits sum to zero


However, it also delete values that do not sum to zero

 Sub DeleteRows_SummingtoZero()
 Dim rw As Long, i As Long
 rw = Cells(Rows.Count, 1).End(xlUp).Row
 For i = rw To 2 Step -1
 If Application.Sum(Cells(i, 9)) = 0 Then
 Rows(i).Delete
 End If
 Next

 End Sub

See link below

Dropbox - File Deleted - Simplify your life

www.dropbox.com

I have also posted on Macro to delete Rows where Values add up to zero In Col H (excelforum.com)

Macro to delete all rows that sum to zero

I have a macro below to delete all rows there the debits and credits sum to zero However, it also delete values that do not sum to zero Sub DeleteRows_SummingtoZero() Dim rw As Long, i As Long rw = Cells(Rows.Count, 1).End(xlUp).Row For i = rw To 2 Step -1 If Application.Sum(Cells(i...

www.mrexcel.com

It would be appreciated if someone could kindly assist me

 

[howard]

Sorry for not replying Joe

I am trying to sum all the items in Col H that add up to Zero and only delete those rows that add up to zero and leaving those that do not add up to zero



I amended my code to

 If Application.Sum(Cells(i, 8)) = 0 Then

 

[Joe4]

You can only checking one cell with that line of code (Cells(i , 8) is just one single cell, not a range).
So the SUM function serves no purpose here. It is overkill and unnecessary You can just use:

If Cells(i, 8) = 0 Then

I already explained this in my first reply.

 

[Joe4]

I am trying to sum all the items in Col H that add up to Zero and only delete those rows that add up to zero and leaving those that do not add up to zero

As was noted, you do not have any zeroes in column H.

Are you trying to find which combinations of values in column H add up to zero and delete those rows?
If so, your code is nowhere close to doing anything like that, as it is considering each row indepedently.

Perhaps you should walk us through the example you posted, and let us know some examples of which rows should be deleted and why.

 

[howard]

Hi Joe

I am trying to find which combinations of values in column H add up to zero and delete those rows

I have indicated in Col I number for eg 1 which will show those values in Col H for the items marked 1 that sum up to zero, the same goes for item mark 2, 3, 4 etc

I only need the items that do not sum up to zero

I made my sample size smaller to make it more simple

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[JackDanIce]

Thanks Jackdanice for your suggestion but there are no zero Values in Col H

I am trying to find which combinations of values in column H add up to zero and delete those rows

Your statements contradict.

In the first file you posted, it doesn't contained any 0 values in column H, whether the debit + credit = 0, the formula you use in that column does not result in a 0 value for any row.

If you recorded a macro as suggested but it didn't delete any rows, is it because you do not have any 0 values in column H?

You are not being clear what H contains, vs what you say it contains, vs what the files you post contains, so it's difficult to suggest anything that works.

Also, your formulas have "=+" in first file, in column H and there isn't any need for the "+", just like you read 6 as 6 and not +6

 

[howard]

I will have a reloook at file tomorrow and try and make it as clear as possible what I am trying to achieve

 

[Joe4]

I am guessing what you mean is that if you have 1000 in on one row in column H, and -1000 on another row in column H, that they cancel each other out, and you want each of those rows deleted. Is that correct? (If so, that is very different than the impression we got from your initial question).

If that is correct, are you also looking for combinations, i.e. If you have -750, -250, and 1000, that all three of those cancel each other out.
If you can have that, that is something that I think would be very complex (as you have to try every single combination). I would have no idea how to best do that.

If it is just a straight one-for-one cancellation, that may be do-able.
However, what about if you had two records of 1000 and one -1000. How do we know which of the two 1000 records to cancel out, and which one to leave?

 

[JackDanIce]

I will have a reloook at file tomorrow and try and make it as clear as possible what I am trying to achieve

Make sure you have 0 values and check they are found if you apply a filter (filters are faster than loops, hence reason for suggesting record this, but you need to check your data contains the thing you want to filter or it's pointless, i.e. why you want to delete zero values in column H, but your column H doesn't have any in the file you posted)

Double check every assumption you make as anyone reading doesn't have your understanding of what you mean

 

[howard]

Hi Joe

The Vast majority of the transactions will have a debit and a corresponding credit i.e if I 000 on one row in column H, and -1000 on another row in column H, they cancel each other out, and I want each of those rows deleted.

I there are two records of 1000 and one -1000, take the first occurrence of the 1000 to cancel out with the -1000

 

[Joe4]

Try this and see if it works for you:

Sub MyDeleteMacro()

    Dim Found
    Dim r As Long
    Dim lr As Long
    
    Application.ScreenUpdating = False
    
'   Determine which row to start on
    r = 2
    
'   Loop through data in column H
    Do
'       Exit loop once column H is empty
        If Cells(r, "H") = "" Then Exit Do
'       Find last row with data in column H
        lr = Cells(Rows.Count, "H").End(xlUp).Row
'       Check for negative of value of current row in data beneath
        Set Found = Range(Cells(r + 1, "H"), Cells(lr, "H")).Find(Cells(r, "H") * -1, , , xlWhole)
'       Check to see if value not found
        If Found Is Nothing Then
'           Move to next row
            r = r + 1
        Else
'           Delete rows
            Rows(Found.Row).Delete
            Rows(r).Delete
        End If
     Loop

    Application.ScreenUpdating = True

End Sub