Find text from range and delete row

[Tom]

Hi all,
I have a range on sheet1 ("A12:A19"). I need to be able to look at that list
and if any name from that list is found on sheet2 ("J3:J400") then delete the
entire row that it is found in.

Thanks!

 

[Mike H]

Tom,

Right click sheet 1 tab, view code and paste this in and ruen it:-

Sub stance()
Dim MyRange As Range
Set MyRange = Sheets("Sheet2").Range("J3:J400")
For x = 19 To 12 Step -1
myvalue = Cells(x, 1).Value
For Each c In MyRange
If myvalue = c.Value Then
Rows(x).EntireRow.Delete
Exit For
End If
Next
Next
End Sub

Mike

 

[Tom]

Mike,
Thanks for the reply! Your code is actually working backwards to what I was
actually needing - maybe I didn't explain the situation right? The range of
names on Sheet1 is what to look for on sheet2. It is the found matches (rows)
on sheet2 that I need to be deleted. This code actually deletes my list on
sheet1. It works...just opposite of what I need.
Look at name on sheet1 ("A12:A19")
Find the match on sheet2 ("J3:J400")
Delete the row on sheet2 if any of the names matches anything in Sheet1
A12:A19

Sorry if I didn't explain myself right the first time around!

Thanks!

 

[Mike H]

Tom,

Converted to work the other way around which now means you paste it into
sheet 2.

Sub stance()
Dim MyRange As Range
Set MyRange = Sheets("Sheet1").Range("A12:A19")
For x = 400 To 3 Step -1
myvalue = Cells(x, 10).Value
For Each c In MyRange
If myvalue = c.Value Then
Rows(x).EntireRow.Delete
Exit For
End If
Next
Next
End Sub

Mike

 

[Tom]

That did it....thanks Mike, your help is appreciated!

 

[Mike H]

Your welcome and thanks for the feedback

That did it....thanks Mike, your help is appreciated!