Excel - A macro to compare and delete rows

October 2017




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.
Published by aakai1056. Latest update on September 24, 2012 at 08:04 AM by jak58.
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).