I have a Macro that pulls data from a text file into a spreadsheet. I need
new to delete some rows that don't contain certain data.

Office Number for example. If the office number on my spreadsheet is not in
a list, I need to delete the row. I can use a loop, but my list has about
40 offices.

I tried using Lookup, but it doesn't quite work. I think my lookup command
is a little off, I only want to compare to one column, Column A in Office.

Here is the part of the code. I am deleting from the bottom up.

Y = Range("XY61!A" & Rows.Count).End(xlUp).Row
Do While Y < 6
Test = Application.Lookup(Range("XY61!B" & Y),
Range("Office!$A$2:$A$84"), Range("Office!$A$2:$A$84"))
If IsError(Test) Then
Range("XY61!B" & Y).EntireRow.Delete
End If
Y = Y - 1
Loop

Thank you in advance.

You will need to loop through the data pulled in from the
text file to search for matches. Use the Find command to
search the 40 offices for a match to the cell in the text
data. If there is no match (can't find it) then delete
that row.

BOL
David C
-----Original Message-----
I have a Macro that pulls data from a text file into a
spreadsheet. I need
new to delete some rows that don't contain certain data.

Office Number for example. If the office number on my
spreadsheet is not in
a list, I need to delete the row. I can use a loop, but
my list has about
40 offices.

I tried using Lookup, but it doesn't quite work. I think
my lookup command
is a little off, I only want to compare to one column,
Column A in Office.

Here is the part of the code. I am deleting from the
bottom up.

Y = Range("XY61!A" & Rows.Count).End(xlUp).Row
Do While Y < 6
Test = Application.Lookup(Range("XY61!B" & Y),
Range("Office!$A$2:$A$84"), Range("Office!$A$2:$A$84"))
If IsError(Test) Then
Range("XY61!B" & Y).EntireRow.Delete
End If
Y = Y - 1
Loop

Thank you in advance.



.

Appreciate the answer, Find is not what I am looking for. I know I am close
with the formula. I already imported the data. I have the loop in place,
just need the formula for "Test" looked at.




"DavidC" wrote in message
...
You will need to loop through the data pulled in from the
text file to search for matches. Use the Find command to
search the 40 offices for a match to the cell in the text
data. If there is no match (can't find it) then delete
that row.

BOL
David C
-----Original Message-----
I have a Macro that pulls data from a text file into a
spreadsheet. I need
new to delete some rows that don't contain certain data.

Office Number for example. If the office number on my
spreadsheet is not in
a list, I need to delete the row. I can use a loop, but
my list has about
40 offices.

I tried using Lookup, but it doesn't quite work. I think
my lookup command
is a little off, I only want to compare to one column,
Column A in Office.

Here is the part of the code. I am deleting from the
bottom up.

Y = Range("XY61!A" & Rows.Count).End(xlUp).Row
Do While Y < 6
Test = Application.Lookup(Range("XY61!B" & Y),
Range("Office!$A$2:$A$84"), Range("Office!$A$2:$A$84"))
If IsError(Test) Then
Range("XY61!B" & Y).EntireRow.Delete
End If
Y = Y - 1
Loop

Thank you in advance.



.

Try this code it seems to do the job. It replaces your
test=....

Dim a As Integer, c As Variant

Y = Range("XY61!A" & Rows.Count).End(xlUp).Row
Do While Y < 6
a = ActiveSheet.Range("xy61!B" & Y).Value
With Worksheets("office").Range("a2:a84")
Set c = .Find(a, LookIn:=xlValues)
If c Is Nothing Then
Range("XY61!B" & Y).EntireRow.Delete

Else

End If

End With

Y = Y - 1
Loop

BOL
DavidC

-----Original Message-----
Appreciate the answer, Find is not what I am looking
for. I know I am close
with the formula. I already imported the data. I have
the loop in place,
just need the formula for "Test" looked at.




"DavidC" wrote in
message
...
You will need to loop through the data pulled in from
the
text file to search for matches. Use the Find command
to
search the 40 offices for a match to the cell in the
text
data. If there is no match (can't find it) then delete
that row.

BOL
David C
-----Original Message-----
I have a Macro that pulls data from a text file into a
spreadsheet. I need
new to delete some rows that don't contain certain
data.

Office Number for example. If the office number on my
spreadsheet is not in
a list, I need to delete the row. I can use a loop,
but
my list has about
40 offices.

I tried using Lookup, but it doesn't quite work. I
think
my lookup command
is a little off, I only want to compare to one column,
Column A in Office.

Here is the part of the code. I am deleting from the
bottom up.

Y = Range("XY61!A" & Rows.Count).End(xlUp).Row
Do While Y < 6
Test = Application.Lookup(Range("XY61!B" & Y),
Range("Office!$A$2:$A$84"), Range("Office!$A$2:$A$84"))
If IsError(Test) Then
Range("XY61!B" & Y).EntireRow.Delete
End If
Y = Y - 1
Loop

Thank you in advance.



.



.

Aaaah. Now I get it.

Thanks David.


wrote in message
...
Try this code it seems to do the job. It replaces your
test=....

Dim a As Integer, c As Variant

Y = Range("XY61!A" & Rows.Count).End(xlUp).Row
Do While Y < 6
a = ActiveSheet.Range("xy61!B" & Y).Value
With Worksheets("office").Range("a2:a84")
Set c = .Find(a, LookIn:=xlValues)
If c Is Nothing Then
Range("XY61!B" & Y).EntireRow.Delete

Else

End If

End With

Y = Y - 1
Loop

BOL
DavidC

-----Original Message-----
Appreciate the answer, Find is not what I am looking
for. I know I am close
with the formula. I already imported the data. I have
the loop in place,
just need the formula for "Test" looked at.




"DavidC" wrote in
message
...
You will need to loop through the data pulled in from
the
text file to search for matches. Use the Find command
to
search the 40 offices for a match to the cell in the
text
data. If there is no match (can't find it) then delete
that row.

BOL
David C
-----Original Message-----
I have a Macro that pulls data from a text file into a
spreadsheet. I need
new to delete some rows that don't contain certain
data.

Office Number for example. If the office number on my
spreadsheet is not in
a list, I need to delete the row. I can use a loop,
but
my list has about
40 offices.

I tried using Lookup, but it doesn't quite work. I
think
my lookup command
is a little off, I only want to compare to one column,
Column A in Office.

Here is the part of the code. I am deleting from the
bottom up.

Y = Range("XY61!A" & Rows.Count).End(xlUp).Row
Do While Y < 6
Test = Application.Lookup(Range("XY61!B" & Y),
Range("Office!$A$2:$A$84"), Range("Office!$A$2:$A$84"))
If IsError(Test) Then
Range("XY61!B" & Y).EntireRow.Delete
End If
Y = Y - 1
Loop

Thank you in advance.



.



.