# Macro to delete rows where values add up to Zero

#### howard

##### Well-known Member
I have control numbers in Col E and Debit value in Col F & credit balances in Col E

Where the control numbers are the same in Col E values in col F - the values in Col G = zero for the same control number then these rows must be deleted

I am looking for a macro to do this

Your assistance in this regard is most appreciated

See sample data below

Excel Workbook
EFG
1ControlDebit BalanceCredit Bal
213/000212625.1
313/000212625.1
418/00619186.52
518/006100
618/0065173.92
718/00610847.18
818/0069965.02
918/00638550
1018/0061711.8
1118/00698159.54
1213/0008277903
1313/0008293844.7
14
15
Sheet1

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Is this an accounting General Journal? Because if it is, then you shouldn't be deleting things from it. The rule is that for every Debited account, another account needs to be Credited. If the sum of the debit column doesn't add up to the same value as the sum of the credit column, then you are doing it wrong.

If I'm wrong and this is not a general journal for accounting, then try this.
Code:
``````lastRow = Range("E" & Rows.Count).End(xlup).Row
i = 2
Do Until i > lastRow
ii = 2
Do Until ii > lastRow
If Range("E" & i).value = Range("E" & ii).value And _
Range("F" & i).value = Range("G" & ii).value Then

'I am not sure if the following delete row code will work.
Rows(i).delete
Rows(ii-1).delete

i = i -1
ii = 1
lastRow = Range("E" & Rows.Count).End(xlup).Row
End If
ii = ii + 1
Loop
i = i + 1
Loop``````

Last edited:
Thanks for the reply. The sample data in my previous file is not a general Journal. This is a schedule and once a debits - the credits are zero and the Control number is the same, the relevant rows can be deleted

I have run your macro and no rows are being deleted-see sample data below

Excel 2012
EFG
1ControlDebit BalanceCredit Bal
213/000212625.1
313/000617242.51
413/00729867.61
511/00035173.92
611/000710847.18
712/00029965.02
812/000338550
912/00041711.8
1012/0006277903
1113/0006277903
1213/0002293844.7
1313/000411148.06
1413/0008261807.01
15
16
Sheet1

Sorry but I'm still confused. I'm looking at E2 right now. Since E2 and E3 have the same date, I want to look at Debit balance. If the debit balance of F2 is equal to the credit balance of G3, then you want to delete both of those rows. In this case, these rows don't get deleted because the debit balance of F2 does not equal the credit balance of G3. But lets look at another row. Take a look at E10 and E11. Those have the same dates. G10 has a credit of 277903. F11 has a debit of 277903. Since the debit and credit match, you want to delete those rows(rows 10 and 11).

Are my assumptions thus far correct? If they are, then the code I gave you should have worked. I don't have time right now to troubleshoot it, but I will later tonight. Please reply before tonight to let me know if my assumptions are correct.

Thanks for the reply. My Apologies, me Codes in Cool E for the sample data was incorrect

If the Control Number in Col E is the same and the vales in Col F - Col G pertaining to these control numbers = zero , then the rows must be deleted

Se Sample Data below

Excel 2012
EFG
1ControlDebit BalanceCredit Bal
213/000212,625.10
313/000217,242.51
413/000229867.61
511/00035173.92
611/000710847.18
712/00029965.02
812/000338550
912/00041711.8
1013/0006277,903.00
1113/0006277,903.00
1213/0002293,844.66
1313/000411148.06
1413/0008261807.01
15
Sheet1

After Running the Macro, this is what it looks like

Excel 2012
EFG
113/000212,625.10
213/000217,242.51
311/00035173.92
411/000710847.18
512/00029965.02
612/000338550
712/00041711.8
813/0002293,844.66
913/000411148.06
1013/0008261807.01
11
12
13
14
Sheet1

The two debit values pertaining to 13/0002 should also have been deleted. The headers in Row 1 must not be deleted

Kindly check & amend code

I don't think i understand. Here is the code that I'm about to write for you. First the vba code should sum all the debit values that pertain to a certain date. Then the vba code should sum all of the credit values that pertain to a certain date. If the sum of debits for a certain date equal the sum of credits for a certain date, then the vba code will delete all of those rows with the certain date? Am I right on my assumptions?

So in your most recent dataset, it should delete rows 2, 3, 4, 10, and 11.

I'm going to write this code and go to bed. If this is not what you wanted, then I'll take another look at it tomorrow.

Here is the macro...
Code:
``````    lastRow = Range("E" & Rows.Count).End(xlUp).Row
i = 2
Do Until i > lastRow
myDate = Range("E" & i).Value
myDebit = Range("F" & i).Value
myCredit = Range("G" & i).Value
ii = i + 1
Do Until ii > lastRow
If myDate = Range("E" & ii).Value Then
myDebit = myDebit + Range("F" & ii).Value
myCredit = myCredit + Range("G" & ii).Value
End If
ii = ii + 1
Loop
If myDebit = myCredit Then
ii = 2
Do Until ii > lastRow
If myDate = Range("E" & ii).Value Then
Rows(ii).Delete
ii = 1
End If
ii = ii + 1
Loop
i = 1
End If
lastRow = Range("E" & Rows.Count).End(xlUp).Row
i = i + 1
Loop``````
Here is the dataSet I used...
 E F G 1 Control Debit Balance Credit Bal 2 13/0002 12,625.10 3 13/0002 17,242.51 4 13/0002 29867.61 5 11/0003 5173.92 6 11/0007 10847.18 7 12/0002 9965.02 8 12/0003 38550 9 12/0004 1711.8 10 13/0006 277,903.00 11 13/0006 277,903.00 12 13/0002 293,844.66 13 13/0004 11148.06 14 13/0008 261807.01

<tbody>
</tbody>
I highlighted red the cells that will get deleted when you run the macro.
Notice where I highlighted green.
The sum of F2 and F3 is 29867.61
G4 has a credit of 29867.61
But they did not get deleted.
The reason they didn't get deleted is because E12 contains the same date as E2 and E3.
Therefore the total debits for "13/0002" equals 29867.61
The total credits for "13/0002" equals 323712.27
That is why they do not get deleted.
Hope this code is exactly what you wanted.

Last edited:
In my last post, I summed all of the debits with the same Control number. I summed all of the credits with the same Control number. It didn't matter where they were in the column. If they had the same control number, it was part of the sum. Now in case I was wrong in that assumption, I modified the code to do something different. This might be what you were trying to do. Here is the macro.
Code:
``````    lastRow = Range("E" & Rows.Count).End(xlUp).Row
i = 2
Do Until i > lastRow
myDate = Range("E" & i).Value
myDebit = Range("F" & i).Value
myCredit = Range("G" & i).Value
ii = i + 1
Do Until myDate <> Range("E" & ii).Value
If myDate = Range("E" & ii).Value Then
myDebit = myDebit + Range("F" & ii).Value
myCredit = myCredit + Range("G" & ii).Value
End If
ii = ii + 1
Loop
If myDebit = myCredit Then
Do Until myDate <> Range("E" & i).Value
If myDate = Range("E" & i).Value Then
Rows(i).Delete
End If
Loop
i = 1
[COLOR=#008000]lastRow = Range("E" & Rows.Count).End(xlUp).Row[/COLOR]
End If
i = i + 1
Loop``````
Here is the dataSet I used.
 E F G 1 Control Debit Balance Credit Bal 2 13/0002 12,625.10 3 13/0002 17,242.51 4 13/0002 29867.61 5 11/0003 5173.92 6 11/0007 10847.18 7 12/0002 9965.02 8 12/0003 38550 9 12/0004 1711.8 10 13/0006 277,903.00 11 13/0006 277,903.00 12 13/0002 293,844.66 13 13/0004 11148.06 14 13/0008 261807.01

<tbody>
</tbody>
I highlighted red the rows that will be deleted. Notice row 12 does not get deleted. That is because the code only sums the credits and debits of immediate control numbers. So in other words, E2, E3, and E4 have the same control numbers. E5 does not have the same control number; therefore, it stops evaluating any rows after E4. It sums the credits and debits up to E4 only. If the credits equal the debits, then it will delete those rows only.

I hope one of these 2 codes I gave you are what you are looking for. Notice in the new vba code where I highlighted a line of code green. I moved it into that if statement from outside of that if statement so that it doesn't have to run that code unless it has to. This way it saves on processing time.

Last edited:
Thanks for all the help. Code works perfectly

Replies
2
Views
142
Replies
1
Views
468
Replies
6
Views
452
Replies
0
Views
514
Replies
1
Views
78

1,219,905
Messages
6,150,901
Members
450,991
Latest member
ExcelDoer

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back