1
Thanks

A few words of thanks would be greatly appreciated.

Excel - A macro to compare and delete rows




Issue


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.

Solution


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
  • 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



Thanks to rizvisa1 for this tip.
1
Thanks

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
This document, titled « Excel - A macro to compare and delete rows », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).

0 Comments