Log In
Register
Home
New Member
January 23, 2019
Offline
I am trying to put together some VB code which will compare debits and credits in two columns and when found it should delete both rows.
A debit and a credit always has a blanh cell in the other column. Attached is my attempt. I can get the first debit and credit and delete both rows but what I cannot do is return to the first loop to start in looking in the debits coumn again.
Can you please assist
Attached is my attempt
October 5, 2010
Offline
Hi Hannes,
There's no workbook attached.
Regards
Phil
New Member
January 23, 2019
Offline
Hi Phil,
I obviously did something wrong when attached my workbook
I tried again to drag my w/book to below
It is called dountiloop1.xlsx
Regards
Hannes
VIP
Trusted Members
June 25, 2016
Offline
Hi Hannes
When requesting for a macro solution it is best if you can supply the actual file (you can change the values if it contain sensitive info)
We need to see the actual format. Otherwise it is very difficult to develop the macro.
Sunny
December 21, 2018
Offline
if you want to delete rows, you can't loop through rows from the top and delete. for example, looping through rows and delete row 5, row 6 shifts up, the code will then go to the next row missing the original row 6.
When deleting rows with a loop, you would have to start from the bottom up.
you can clear contents though
[code]
Sub Button1_Click()
Dim rng1 As Range, rng2 As Range
Dim a As Range, b As Range
Set rng2 = Range("B:B").SpecialCells(xlCellTypeConstants, 23)
Set rng1 = Range("A:A").SpecialCells(xlCellTypeConstants, 23)
For Each a In rng1.Cells
Set b = rng2.Find(what:=a, lookat:=xlWhole)
If b Is Nothing Then
Else
a.ClearContents
b.ClearContents
End If
Next a
End Sub
[/code]
You can also make the values of a & b ="xxx" then filter out xxx
1 Guest(s)
