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]

hi Joe

You code works if the Debits and credits are beneath each other. If I could get the debit say 1000 and correspoding credit -1000 below each otherr, this will work

 

[Joe4]

Sorry, I have been away for over a week.
If you still need help with this, please explain in more detail what you are still looking for, using images to illustrate what you mean.

 

[howard]

HI Joe

Just to let you know that I have made use of a formula to return True or False for contra entries

 

[Joe4]

OK, can you re-mark the actual post that had the solution to the original question asked in this thread?
The one you just marked does not have any solution.

 

[howard]

Hi Joe


I just tested your code on the data below and cannot get i to delete the items where thee is a debit in Col H as well as a corresponding Credit

as can be seen H2 & H3 Contra and should be deleted

Kindly test and advise

Delete Debit value where Corresponing credit Value.xlsm
	A	B	C	D	E	F	G	H
1	Reference	Source	Run	A/C	Date	Debit	Credit	Balance
2	0053157	Purchase Ordering			07/06/2021	1356	0	1356
3	56	Sales Ledger			22/06/2021	0	1356	-1356
4	662P	Purchase Ledger			30/06/2021	103.5	0	103.5
5	BRTW MAY21	Purchase Ledger			14/06/2021	0	461.49	-461.49
6	582K	Nominal Ledger			29/06/2021	6044.4	0	6044.4
7	598G	Nominal Ledger			30/06/2021	0	103.6	-103.6
8	53K	Purchase Ledger			30/06/2021	6232.6	0	6232.6
Sheet1

Cell Formulas
Range	Formula
H2:H8	H2	=+F2-G2

 

[Joe4]

I ran the code against the sample you posted, and it deleted the first two data lines for me.
Does it not do that for you?

If it does not delete those two rows, then you probabaly have a floating-point precision issue going on.
That can be easily remedied by changing your formulas in column H like this:

=ROUND(F2-G2,2)

 

[howard]

Thanks for the formula. Code runs perfectly now

 

[Joe4]

You are welcome.

Since the code that was posted originally did indeed work once we fixed up the data/formula, I have re-marked that as the solution, as that is the reply that most accurately answers the original question you posted (the latest fix to your data does not answer the original question asked).