script that will compare a specific column in sheet 1 with a column in sheet 2.

[p.f.]

Hello,
I am looking for a script that will compare a specific column in sheet 1 with a column in sheet 2.
I have a report with names of people that login to certain machines and I need to compare it to a database in sheet 2.
If the names in sheet 1 are also in sheet 2, then delete the row with that name in sheet 1. If the name is not in sheet 2 then leave it and go to the next one.
Compare column B in sheet one with column B in sheet 2.

I have a script (below) that almost does what I need except it doesn't always work properly. The reason my script runs 15 times is because sometimes it doesn't pick all the names but if I run it again it does... it's weird...
Please let me know if there is a better way to do this. Thanks a lot!

Script:

Please Login or Register  to view this content.

[p.f.]

Attached is a sample.
Sheet 1 is the report I receive every day that has new names. I need to compare these names with sheet 2 (database) and if any of the names in sheet 1 exist in sheet 2, delete the row and continue. Whatever is left on sheet 1 after the script runs is what's not on sheet 2.

[Fotis1991]

Hi and welcome to the forum. Pls take few minutes to read the forum rules, because:

Your post does not comply with rule 3 of our forum.Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Select your code and click the [#] button at the top of the post window (if you are editing an existing post, press Go Advanced to see the [#] button).

[p.f.]

All done

[AB33]

This should do. It is a bit slow as it has to delete the entire row. I would suggest just clear the single cell, instead of deleting and this will speed up the code.

Please Login or Register  to view this content.

[p.f.]

It's working but it's only doing half at a time. I pasted 30000 lines and after running the macro I have 15000 left. I run it again and it does half again...
Any thoughts?
Thanks.

[AB33]

I do not understand by "half". On the given sample does the code matches both sheets and delete the match from sheet1, if no what is missing?

[jindon]

How about?

Please Login or Register  to view this content.

[AB33]

I have got the same result by running a different code.

Please Login or Register  to view this content.

[jindon]

It's working but it's only doing half at a time.

It is a well-known issue when you delete row(s).
The loop needs to be backward from the bottom, otherwise it only deletes one line when consecutive lines need to be deleted.

[AB33]

Jindon,
Thanks!
I completely forgotten it.
Change this line of code # 5

Please Login or Register  to view this content.

INTO

Please Login or Register  to view this content.

[p.f.]

This is brilliant! Thank you all very much! All is working.