Find text from range and delete row

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tom - 28 Nov 2007 10:05 GMT

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!

Reply to this Message

Mike H - 28 Nov 2007 10:47 GMT

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

> 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!

Reply to this Message

Tom - 28 Nov 2007 12:28 GMT

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!

> Tom,
>
[quoted text clipped - 22 lines]
> >
> > Thanks!

Reply to this Message

Mike H - 28 Nov 2007 12:50 GMT

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

> Mike,
> Thanks for the reply! Your code is actually working backwards to what I was
[quoted text clipped - 37 lines]
> > >
> > > Thanks!

Reply to this Message

Tom - 28 Nov 2007 13:04 GMT

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

> Tom,
>
[quoted text clipped - 58 lines]
> > > >
> > > > Thanks!

Reply to this Message

Mike H - 28 Nov 2007 13:06 GMT

Your welcome and thanks for the feedback

> That did it....thanks Mike, your help is appreciated!
>
[quoted text clipped - 60 lines]
> > > > >
> > > > > Thanks!

Reply to this Message