Delete rows based on non-matching criteria (Exceptions)

[Rabbitoh]

G'day Code Masters

I have a list of names in Column B (from B6) on Sheet2 which represent the names I always want retained. There is only one instance of each name.

I also have a list of names in Column G (from G5) on Sheet1. This list is produced each month, with some names returning and new ones appearing. There can be multiple instances of the same name.

On Sheet1, I always want to keep (the entire row of) those entries that match the names on Sheet2. All the rest are to be deleted (delete the entire row).

Example:

If Steven Hawking appears on Sheet1, and his name is also on Sheet2 (the names I always want to retain), then all instances of his name stay on Sheet1.

If Mick Jagger appears on Sheet1, and his name is NOT on Sheet2, then for all instances of his name, the entire row gets deleted.

The code continues looping through the entire name list on Sheet1 until finished. A message box at the end advising the total entries removed would be useful but is not essential.

Note: I have some working code already in the workbook module but unfortunately it does the process the wrong way round!

Thanks

[davesexcel]

See if this works.

Please Login or Register  to view this content.

[sintek]

Another Option...

Please Login or Register  to view this content.

[Rabbitoh]

This is excellent and does exactly what is required. Thank you

[Rabbitoh]

Thank you Sintek. This one is a variation on the last one you so excellently provided me. Unfortunately, it deletes everything on sheet1, including the ones that were to be kept. Want to have another go so I can add to your rep?

[sintek]

Unfortunately, it deletes everything on sheet1, including the ones that were to be kept

That is because your calculation is set to manual...Go to settings and change it to automatic and the code will work correctly...
Or use this snippet...

Please Login or Register  to view this content.

[Rabbitoh]

Wow, I didn't know there was such a thing. Have changed and now works excellent. Thank you once again.

[sintek]

Glad to have contributed...Tx for rep +