Multiple find and delete between two cols in two worksheets

[terryhenderson]

I have Sheet1 which has almost 800 thousand product names in column A, with a header "Existing Products" in A1, and sorted A-Z. I also have Sheet2 which has names for new products to be tested whether we already have them in Sheet1 before further data manipulation. New product names are in column C in Sheet2 with a header "New Products" in C1.

What I'm after is a macro that takes every product name from column A in Sheet1, and searches for it in column C in Sheet2. If it finds it in Sheet2, then the name should be deleted. This loop should continue until the end of existing products names in Sheet1. Once the entire process is completed, Column C in Sheet2 should be sorted A-Z to eliminate any empty cell.

Can anyone help me achieve this please?

Thanks in advance...

[jolivanes]

I found the code below. However, it compares 2 Columns on the same sheet.
I don't want to try to change it so it works on a different sheet (in case I really blub it) but if you're
willing to, you could copy both Columns into a new workbook and try it. If it works you can copy the finished column
back to the original sheet.
Or someone will jump in with a solution.


Thanks to GTO

Please Login or Register  to view this content.

[terryhenderson]

Thank you very much jolivanes for your invaluable input. In fact, I tried running this sub, but compiling error occurred as sub or function not defined. Due to my little knowledge about vba, I had to create a macro in which I placed the script above, but it is still giving me the same error highlighting "RangFound" in #Set rngLastCell = RangeFound(.Columns(1), , .Cells(1, 1))#

Any suggestions?

[jolivanes]

terryhenderson
I am not good at that kind of stuff either.
Copy your columns into attached workbook and see what happens.
Maybe someone reading this thread can pinpoint the problem.
Sorry about that but I am still learning also.
John

[terryhenderson]

Thank you John very much for that file, which does have RangeFound function that wasn't posted in your previous reply. I ran it and it does work great for me. I don't think I will ask for more as I can put both columns in one sheet instead of two, changing the procedure of data manipulation a bit, which is harmless of course.

Once again, thank you John for your sincere cooperation.

[jolivanes]

Glad it worked for you Terry.
Strange that it did not copy part of the code over.
As long as it works for you within a reasonable time with such a big file.
Good luck
John

[jolivanes]

Terry
It looks like all you have to do is add two lines to the code for it to work between Sheet1 and Sheet2(see below)

Please Login or Register  to view this content.

If Mark (GTO) reads this he might concur with this!!!