Help remove duplicate rows

[wilkisa]

We receive a large sheet from an outside source. It has 157 columns (A - FA), and 4041 rows.

The vendor name is in column C and the vendor address is in column D. I need a macro that will find duplicates and delete the entire row.

Example:

Col. C...Wonder Widgets...Col. D...123 Main
Col. C...Wonder Widgets...Col. D...456 Elm
Col. C...Wonder Widgets...Col. D...123 Main

I want to delete only the second occurance of Wonder Widgets at 123 Main. The other two Wonder Widgets should remain in the sheet.

Thanks in advance for any help,
Shirlene

 

[brettvba]

Tom your code will be making excel not respond because it has a large load it is still working mircosoft just deems it as "Not Responding" if she leaves it to do its thing it will finish eventually I had one with loops and 45000 rows and it only took about half hour i can post the code if needed.

 

[Brabantio]

On 2002-04-23 19:40, brettvba wrote:
Tom your code will be making excel not respond because it has a large load it is still working mircosoft just deems it as "Not Responding" if she leaves it to do its thing it will finish eventually I had one with loops and 45000 rows and it only took about half hour i can post the code if needed.

Run time is always a problem if loops are used on large amounts of data.
For this particular question a loop is not necessary (and not desirable).
You say that you have a macro that "Only"(!) takes half an hour.
There could well be a more efficient way of writing the code. Post it if you wish.

 

[Legacy 98055]

Tom loves loops...
A loop a day keeps the boss away...
Am still an inexperienced programmer.
Will loop until it's worth it to me to improve my code. A millisecond or bitty-byte of ram here or there does not matter for what I do.
Tom a.k.a. "LOOP KING"

 

[Brabantio]

On 2002-04-23 20:07, TsTom wrote:
Tom loves loops...
A loop a day keeps the boss away...
Am still an inexperienced programmer.
Will loop until it's worth it to me to improve my code. A millisecond or bitty-byte of ram here or there does not matter for what I do.
Tom a.k.a. "LOOP KING"

But it is usually not just a "millisecond or bitty-byte of ram here or there".
Brettvaba's macro that "only" takes half an hour is quite often typical of inappropriate use of loops - run times can often be reduced from many minutes to just a few seconds.

It is generally good practice (more efficient) to avoid loops whenever possible and make use instead of available Excel built-in functions.
This also often has the advantage of macros being recordable so that they can be created without any VBA knowledge.

 

[Legacy 98055]

I concede, you are correct!

 

[brettvba]

but it all depends on that the situation is and in my case it was appropriate, but quite right a lot of tuning can speed up a macro from half an hour to 1 minute!

 

[Brabantio]

On 2002-04-23 21:26, brettvba wrote:
but it all depends on that the situation is and in my case it was appropriate, but quite right a lot of tuning can speed up a macro from half an hour to 1 minute!

Would be interested to see your code.

 

[wilkisa]

Brabantio, I tried to record according to the steps you provided and it deleted the entire sheet! So I just copy/pasted the code you provided and it worked like a charm. Thanks to everyone for all you help.

 

[Brabantio]

On 2002-04-24 05:48, wilkisa wrote:
Brabantio, I tried to record according to the steps you provided and it deleted the entire sheet! So I just copy/pasted the code you provided and it worked like a charm. Thanks to everyone for all you help.

You must have done something different from the steps listed.
Try stepping through the macro to see the steps. It is worth seeing how it does it, so that you can use similar procedures in future.