Macro to delete rows where values add up to Zero

[howard]

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
	E	F	G
1	Control	Debit Balance	Credit Bal
2	13/0002	12625.1
3	13/0002		12625.1
4	18/006		19186.52
5	18/006		100
6	18/006		5173.92
7	18/006		10847.18
8	18/006		9965.02
9	18/006		38550
10	18/006		1711.8
11	18/006	98159.54
12	13/0008	277903
13	13/0008		293844.7
14
15
Sheet1

 

[WarPigl3t]

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.

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

 

[howard]

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

Your assistance in resolving this is most appreciated. If you want the sample file, send me your email address via a PM and I will email you the workbook

Excel 2012
	E	F	G
1	Control	Debit Balance	Credit Bal
2	13/0002	12625.1
3	13/0006	17242.51
4	13/007		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	12/0006		277903
11	13/0006	277903
12	13/0002		293844.7
13	13/0004	11148.06
14	13/0008	261807.01
15
16
Sheet1

 

[WarPigl3t]

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.

 

[howard]

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
	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
15
Sheet1

After Running the Macro, this is what it looks like

Excel 2012
	E	F	G
1	13/0002	12,625.10
2	13/0002	17,242.51
3	11/0003		5173.92
4	11/0007		10847.18
5	12/0002		9965.02
6	12/0003		38550
7	12/0004		1711.8
8	13/0002		293,844.66
9	13/0004	11148.06
10	13/0008	261807.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

Your assistance is most appreciated

 

[WarPigl3t]

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.

 

[WarPigl3t]

Here is the macro...

    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.

 

[WarPigl3t]

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.

    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.

 

[howard]

Thanks for all the help. Code works perfectly