| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Rows beased on Values
I have values that are on Sheet1 in Range A5:A100. On
Sheet2 I have values that are in Range in B3:B97. I need a code that will look in the Range on Sheet1 and for each value in that range, if it does not find that value in the Range on sheet2, will delete the value in the range on Sheet1 as well as the entire Row that the value is located on. Thank you Todd |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting Rows beased on Values
Again, not tested with real data:
Dim c As Range, rng As Range For Each c In Sheet1.Range("A5:A100") If Sheet2.Range("B3:B97").Find(c.Value) Is Nothing Then If rng Is Nothing Then Set rng = c Else Set rng = Union(rng, c) End If End If rng.EntireRow.Delete Next -- Vasant "Todd Huttenstine" wrote in message ... I have values that are on Sheet1 in Range A5:A100. On Sheet2 I have values that are in Range in B3:B97. I need a code that will look in the Range on Sheet1 and for each value in that range, if it does not find that value in the Range on sheet2, will delete the value in the range on Sheet1 as well as the entire Row that the value is located on. Thank you Todd |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting Rows beased on Values
Todd,
Very clunky, but it works... Sub TestMe() Dim lRow As Long lRow = Range("A65536").End(xlUp).Row Dim x As Integer Range("A5").Select For x = 5 To lRow If IsError(Application.VLookup(ActiveCell.Value, Worksheets("Sheet2").Range("B3:B97"), 1, False)) Then ActiveCell.EntireRow.Delete ActiveCell.Offset(-1, 0).Select End If ActiveCell.Offset(1, 0).Select Next x End Sub John "Todd Huttenstine" wrote in message ... I have values that are on Sheet1 in Range A5:A100. On Sheet2 I have values that are in Range in B3:B97. I need a code that will look in the Range on Sheet1 and for each value in that range, if it does not find that value in the Range on sheet2, will delete the value in the range on Sheet1 as well as the entire Row that the value is located on. Thank you Todd |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting Rows beased on Values
Thank you it works great. Is there a way to have it
ignore " " (spaces)? The reason I ask is because one of the values in the range on sheet1 had a space after it like "Todd " and the other value in the range on sheet2 didnt have a space after it like "Todd". When the code was run it delete the value "Todd " in the range on sheet1 because they didnt match exactly. Just wondering if it can be doen. Thanx Todd -----Original Message----- Todd, Very clunky, but it works... Sub TestMe() Dim lRow As Long lRow = Range("A65536").End(xlUp).Row Dim x As Integer Range("A5").Select For x = 5 To lRow If IsError(Application.VLookup(ActiveCell.Value, Worksheets("Sheet2").Range("B3:B97"), 1, False)) Then ActiveCell.EntireRow.Delete ActiveCell.Offset(-1, 0).Select End If ActiveCell.Offset(1, 0).Select Next x End Sub John "Todd Huttenstine" wrote in message ... I have values that are on Sheet1 in Range A5:A100. On Sheet2 I have values that are in Range in B3:B97. I need a code that will look in the Range on Sheet1 and for each value in that range, if it does not find that value in the Range on sheet2, will delete the value in the range on Sheet1 as well as the entire Row that the value is located on. Thank you Todd . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting Rows beased on Values
It gives me the error "Object variable or with block not
set" and highlights the below part of the code yellow. rng.EntireRow.Delete -----Original Message----- Again, not tested with real data: Dim c As Range, rng As Range For Each c In Sheet1.Range("A5:A100") If Sheet2.Range("B3:B97").Find(c.Value) Is Nothing Then If rng Is Nothing Then Set rng = c Else Set rng = Union(rng, c) End If End If rng.EntireRow.Delete Next -- Vasant "Todd Huttenstine" wrote in message ... I have values that are on Sheet1 in Range A5:A100. On Sheet2 I have values that are in Range in B3:B97. I need a code that will look in the Range on Sheet1 and for each value in that range, if it does not find that value in the Range on sheet2, will delete the value in the range on Sheet1 as well as the entire Row that the value is located on. Thank you Todd . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting Rows beased on Values
Sorry about that; that line should be outside the loop (after, not before,
the Next statement). -- Vasant "Todd Huttenstine" wrote in message ... It gives me the error "Object variable or with block not set" and highlights the below part of the code yellow. rng.EntireRow.Delete -----Original Message----- Again, not tested with real data: Dim c As Range, rng As Range For Each c In Sheet1.Range("A5:A100") If Sheet2.Range("B3:B97").Find(c.Value) Is Nothing Then If rng Is Nothing Then Set rng = c Else Set rng = Union(rng, c) End If End If rng.EntireRow.Delete Next -- Vasant "Todd Huttenstine" wrote in message ... I have values that are on Sheet1 in Range A5:A100. On Sheet2 I have values that are in Range in B3:B97. I need a code that will look in the Range on Sheet1 and for each value in that range, if it does not find that value in the Range on sheet2, will delete the value in the range on Sheet1 as well as the entire Row that the value is located on. Thank you Todd . |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting Rows beased on Values
Todd,
Just wondering if it can be doen. I haven't seen much that can't be done. If it's absolutely impossible, Tom will find a way around it. Anyway... Try this: If IsError(Application.VLookup(Trim(ActiveCell.Value) , Worksheets("Sheet2").Range("B3:B97"), 1, False)) Then (That's all one line....watch for wordwrap) John "Todd Huttenstine" wrote in message ... Thank you it works great. Is there a way to have it ignore " " (spaces)? The reason I ask is because one of the values in the range on sheet1 had a space after it like "Todd " and the other value in the range on sheet2 didnt have a space after it like "Todd". When the code was run it delete the value "Todd " in the range on sheet1 because they didnt match exactly. Just wondering if it can be doen. Thanx Todd -----Original Message----- Todd, Very clunky, but it works... Sub TestMe() Dim lRow As Long lRow = Range("A65536").End(xlUp).Row Dim x As Integer Range("A5").Select For x = 5 To lRow If IsError(Application.VLookup(ActiveCell.Value, Worksheets("Sheet2").Range("B3:B97"), 1, False)) Then ActiveCell.EntireRow.Delete ActiveCell.Offset(-1, 0).Select End If ActiveCell.Offset(1, 0).Select Next x End Sub John "Todd Huttenstine" wrote in message ... I have values that are on Sheet1 in Range A5:A100. On Sheet2 I have values that are in Range in B3:B97. I need a code that will look in the Range on Sheet1 and for each value in that range, if it does not find that value in the Range on sheet2, will delete the value in the range on Sheet1 as well as the entire Row that the value is located on. Thank you Todd . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Deleting rows based on column values | New Users to Excel | |||
| Deleting rows based on values in a a cell in the row | New Users to Excel | |||
| Macro Help Needed: Comparing cell values and deleting rows | Excel Discussion (Misc queries) | |||
| Deleting rows with specific values | Excel Discussion (Misc queries) | |||
| deleting values in a worksheet without deleting the formulas | Excel Worksheet Functions | |||
![Todd Huttenstine[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
