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

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

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

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


.

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


.

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


.

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


.