Need a macro that will find matching values in column B and delete both rows.

Division Chq# Description Amount
A 24 Bob Co 50.00
B 32 Mike Ltd 60.00
C 40 James 75.00
B 32 CLEARED -60.00
A 24 CLEARED -50.00

Off the top of my head before I go. Slow way round, works.

Record a macro that adds the formula =COUNTIF(B:B,B2) and drag it down, then
copy it and paste values. Then put a filter on, and filter for anything
higher than 1. Then delete all those rows.

That will delete all rows that share a duplicate Chq#

" wrote:

Need a macro that will find matching values in column B and delete both rows.

Division Chq# Description Amount
A 24 Bob Co 50.00
B 32 Mike Ltd 60.00
C 40 James 75.00
B 32 CLEARED -60.00
A 24 CLEARED -50.00

This works for a small data set but it will not complete the processing on a
larger data set. I am working with a workbook that has more than 30,000 rows
each month which causes Excel to hang and crash.
Any other thoughts on the matter?

"PaulW" wrote:

Off the top of my head before I go. Slow way round, works.

Record a macro that adds the formula =COUNTIF(B:B,B2) and drag it down, then
copy it and paste values. Then put a filter on, and filter for anything
higher than 1. Then delete all those rows.

That will delete all rows that share a duplicate Chq#

" wrote:

Need a macro that will find matching values in column B and delete both rows.

Division Chq# Description Amount
A 24 Bob Co 50.00
B 32 Mike Ltd 60.00
C 40 James 75.00
B 32 CLEARED -60.00
A 24 CLEARED -50.00

First off, i'm no expert but I'd do something like

i = 1
x = 1

do until cells(i,2) = ""

do until cells(x,2) = ""

if i < x then
if cells(i,2) = cells(x,2) then
rows(x).delete
rows(i).delete
x = 65000
else
end if
else
end if

x = x + 1
loop
i = i + 1
loop


Basically speaking, it will look at the value in B1, then it will compare
this value to B2, B3, B4, etc, until it hits a cell that thats blank. If it
comes accross a cell that shares a value it will delete both rows. Once it
gets to row 30,000 or whenever cells(x,2) is blank then it will finish the
loop, then it will switch to whats in row B2 and check all those. Followed by
B3, B4, etc.

Note this will delete both rows, so a third reference will be kept. Since X
should always be higher than I deleting this row first shouldn't mess up
anything else. And setting X to 65000 will hopefully point it at a blank cell
that will break the loop. The line about if i < x stops the macro matching
whats in say B5 to whats in B5 and trying to delete it twice (which would
delete both rows 5 and 6)

Hopefully this will help, and might not be exactly what you need, but close
enough to help you.


" wrote:

This works for a small data set but it will not complete the processing on a
larger data set. I am working with a workbook that has more than 30,000 rows
each month which causes Excel to hang and crash.
Any other thoughts on the matter?

"PaulW" wrote:

Off the top of my head before I go. Slow way round, works.

Record a macro that adds the formula =COUNTIF(B:B,B2) and drag it down, then
copy it and paste values. Then put a filter on, and filter for anything
higher than 1. Then delete all those rows.

That will delete all rows that share a duplicate Chq#

" wrote:

Need a macro that will find matching values in column B and delete both rows.

Division Chq# Description Amount
A 24 Bob Co 50.00
B 32 Mike Ltd 60.00
C 40 James 75.00
B 32 CLEARED -60.00
A 24 CLEARED -50.00