Find text from range and delete row
 

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!

Find text from range and delete row
 

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" wrote:

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!

Find text from range and delete row
 

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" wrote:

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" wrote:

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!

Find text from range and delete row
 

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" wrote:

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" wrote:

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" wrote:

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!

Find text from range and delete row
 

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

"Mike H" wrote:

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" wrote:

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" wrote:

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" wrote:

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!

Find text from range and delete row
 

Your welcome and thanks for the feedback

"Tom" wrote:

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

"Mike H" wrote:

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" wrote:

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" wrote:

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" wrote:

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!