| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| find text and delete rows. | Excel Discussion (Misc queries) | |||
| Find and Replace - delete the remainder of the text in the cell after my Find | Excel Programming | |||
| Find and Delete Row from Range | Excel Programming | |||
| find text delete row | Excel Programming | |||
| find and delete text, find a 10-digit number and put it in a textbox | Excel Programming | |||
Linear Mode
