I have 2 worksheets with data that needs to be eliminated on sheet 1.
Sheet 1 column M contains a random list of numbers
Sheet 2 column A contains an organized list of numbers
Need to compare Sheet 1 column M data to Sheet 2 column A data and delete rows on Sheet 1 that match Sheet 2.
Basically each cell on Sheet 1 column M needs to be compared to entire column data of Sheet 2 column A to determine a match. Then delete the row. I am not to familiar with Excel macro coding. I can get to the macro coding section and logically figure out some of the basics but that is it.
Any help is greatly appreciated.
BEFORE DOING THIS SAVE YOUR FILE SAFELY SOME WHERE SO THAT DATA CAN BE RETRIEVED IF THERE IS A MES UP .
2. row no1. in both sheet are having column headings.
I am sure you know where to park the macro. try this macro
the macro is
Sub test()
Dim r2 As Range, j As Long, k As Long, cfind As Range, x
With Worksheets("sheet1")
j = .Range("M2").End(xlDown).Row
For k = j To 2 Step -1
x = .Cells(k, "M").Value
With Worksheets("sheet2")
Set r2 = Range(.Range("A2"), .Range("A2").End(xlDown))
Set cfind = r2.Cells.Find(what:=x, lookat:=xlWhole)
If Not cfind Is Nothing Then
GoTo line1
Else
GoTo nextk
End If
End With
GoTo nextk
line1:
.Cells(k, "m").EntireRow.Delete
nextk:
Next k
End With
End Sub
