Excel - A macro to compare and delete rows

December 2016


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.


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.

Related :

This document entitled « Excel - A macro to compare and delete rows » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.