Macro Question

D

D Van

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.
 
D

DavidC

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
 
D

D Van

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.
 
G

Guest

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
 
D

D VanDerMark

Aaaah. Now I get it.

Thanks David.


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
Click to expand...
 
Top