Solved: Compare 2 sheets and delete matched rows

[gauca001]

Sheet 1 is my master data. Sheet 2 is a newly supplied list. I need to match column D in sheet 2 to column E in sheet 1. Then the macro is to delete the entire matched rows in sheet 2 only, leaving only those unmatched. Attached is a sample file.

Thanks
Tony

[stanleydgrom]

gauca001,

Welcome to the VBA Express Forum.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.




Option Explicit
Sub DeleteMatched()
' stanleydgrom, 05/13/2012
' http://www.vbaexpress.com/forum/showthread.php?t=42150
Dim w1 As Worksheet, w2 As Worksheet
Dim r As Long, lr As Long, fr As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
Set w2 = Worksheets("Sheet2")
lr = w2.Cells(Rows.Count, 4).End(xlUp).Row
For r = lr To 2 Step -1
fr = 0
On Error Resume Next
fr = Application.Match(w2.Cells(r, 4), w1.Columns(5), 0)
On Error GoTo 0
If fr > 0 Then w2.Rows(r).Delete
Next r
w2.Activate
Application.ScreenUpdating = True
End Sub




Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the DeleteMatched macro.


Have a great day,
Stan

[gauca001]

Thanks it works fine.