How to delete credits and debits off the same column?

T

Tif

New Member
Joined
Jul 7, 2009
Messages
22
Hi,

I am trying to go through 5000 odd rows to check if a debit and a credit for the same value exist in column E. Basically, what i need is a macro to check the reference number in column B and delete rows that have the matching debit and a credit for that reference number e:g
<TABLE style="WIDTH: 113pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=151 border=0 x:str><COLGROUP><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2633" width=72><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 59pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=79 height=17>ColB</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 54pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=72>ColE</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19 x:num>401865800</TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="-5754">($5,754.00)</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19 x:num>401865800</TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="5754">$5,754.00 </TD></TR></TBODY></TABLE>

What i need in the above example is that both rows be deleted. Can I pleaseee get some help
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
Is the data sorted ? Like you displayed ? or could it be like the second series shown below..

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:133px;" /><col style="width:105px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:center; ">ColB</td><td style="text-align:center; ">ColE</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:center; ">401865800</td><td style="color:#ff0000; text-align:center; ">($5,754.00)</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:center; ">401865800</td><td style="text-align:center; ">$5,754.00 </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:center; ">ColB</td><td style="text-align:center; ">ColE</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:center; ">401865800</td><td style="color:#ff0000; text-align:center; ">($5,754.00)</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:center; ">607833800</td><td style="text-align:center; ">$3,212.50 </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:center; ">401865800</td><td style="text-align:center; ">$5,754.00 </td></tr></table>
 
Upvote 0
Before
<b>Sheet2</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:118px;" /><col style="width:140px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-family:Verdana; text-align:center; ">ColB</td><td style="font-family:Verdana; text-align:center; ">ColE</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">401865800</td><td style="color:#ff0000; text-align:right; ">($5,754.00)</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">401865800</td><td style="text-align:right; ">$5,754.00 </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">401865800</td><td style="color:#ff0000; text-align:right; ">($5,754.00)</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">607833800</td><td style="text-align:right; ">$3,212.50 </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">401865800</td><td style="text-align:right; ">$5,754.00 </td></tr></table>

AFTER
<b>Sheet2</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:118px;" /><col style="width:140px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-family:Verdana; text-align:center; ">ColB</td><td style="font-family:Verdana; text-align:center; ">ColE</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">607833800</td><td style="text-align:right; ">$3,212.50 </td></tr></table>

Code: 
Private Sub doit_click()
    For x = 2 To Cells(Rows.Count, "B").End(xlUp).Row
        'Get the string to compare
        tacct = Cells(x, 2)
        tamt = Cells(x, 5)
        For x2 = x + 1 To Cells(Rows.Count, "A").End(xlUp).Row
            If Cells(x2, 2) = tacct And tamt + Cells(x2, 5) = 0 Then
                Rows(x2).Delete
                Rows(x).Delete
                x = x - 1
                Exit For
            End If
        Next    'x2
    Next    'x
End Sub
 
Upvote 0
phxsportz said:
Before
<b>Sheet2</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:118px;" /><col style="width:140px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >A</td><td >B</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-family:Verdana; text-align:center; ">ColB</td><td style="font-family:Verdana; text-align:center; ">ColE</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">401865800</td><td style="color:#ff0000; text-align:right; ">($5,754.00)</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">401865800</td><td style="text-align:right; ">$5,754.00 </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">401865800</td><td style="color:#ff0000; text-align:right; ">($5,754.00)</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">607833800</td><td style="text-align:right; ">$3,212.50 </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">401865800</td><td style="text-align:right; ">$5,754.00 </td></tr></table>

AFTER
<b>Sheet2</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:118px;" /><col style="width:140px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >A</td><td >B</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-family:Verdana; text-align:center; ">ColB</td><td style="font-family:Verdana; text-align:center; ">ColE</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">607833800</td><td style="text-align:right; ">$3,212.50 </td></tr></table>

Code: 
Private Sub doit_click()
    For x = 2 To Cells(Rows.Count, "B").End(xlUp).Row
        'Get the string to compare
        tacct = Cells(x, 2)
        tamt = Cells(x, 5)
        For x2 = x + 1 To Cells(Rows.Count, "A").End(xlUp).Row
            If Cells(x2, 2) = tacct And tamt + Cells(x2, 5) = 0 Then
                Rows(x2).Delete
                Rows(x).Delete
                x = x - 1
                Exit For
            End If
        Next    'x2
    Next    'x
End Sub
Click to expand...

Can you do this without VBA and use formula.
 
Upvote 0
kjaan said:
Can you do this without VBA and use formula.
Click to expand...


This is almost exactly what I'm looking for. Would it be possible to create VBA code that would do this without the reference number? Below is an image of a small ledger as an example. The example shown does happen to have a reference number but often one is not available. The goal here would be to find the debit and matching credit(s) and if they foot to zero then delete the entire row(s) containing those debits and credits. This process would hopefully leave only the reconciling items in the ledger. In the example shown below, only the Debits of $29.34, $20.64 and $20.63 (totaling $70.61) would remain to be researched and reconciled. Any help or direction with this VBA code or function would be helpful. Thank you in advance.

a>
 
Upvote 0
can you explain the logic in this code?, what does x represents? , what does x, 2 and x, 5 is looking at?..just trying to understand what the code is doing. thank you
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
564
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
908
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C

Forum statistics

Threads
1,223,790
Messages
6,174,594
Members
452,574
Latest member
hang_and_bang

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top