Macro to compare and delete rows

Clare - May 21, 2010 at 09:01 AM
 Clare - May 21, 2010 at 09:37 AM

I'm a new b when it comes to macros. I'm looking for the best way to compare data in 2 sheets. If data in column A matches on both sheets, I want to delete the entire row from the 2nd sheet.

1 reply

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
May 21, 2010 at 09:15 AM
The best way would be to have a temp column on sheet 2. In that column you can use a VLOOKUP like this

=IF(ISERROR(VLOOKUP(A1, SHeet1!A:A, 1, false)), 0, 1)

Then apply filter on sheet 2 for 1
Delete all the rows that are filtered

Remove filter
Clear out the temp column

You can record these actions via excel macro recorder and it will give you a macro that you can use later too
Great idea! This worked slick. Thanks for your help.