This will work:
One thing I do is I name a range in your lookup spreadsheet. I call it
LookupRange. It makes the lookup easier.


Sub LookupSub()
Dim LURange As Range
Dim Res As Variant
Dim X As Double
Dim Y As Double
Dim DataArray(5000, 1) As Variant

Set LookupRng = Workbooks("Lookup.xlsx").Names("LookUpRange").Refe rsToRange
X = 1
Do While True
If Cells(X, 1).Value = Empty Then Exit Do
Res = Application.VLookup(Cells(X, 1), LookupRng, 7, False)
If Not (IsError(Res)) Then
MsgBox "Found " & Res
Else
Fnd = Fnd + 1
DataArray(Fnd, 1) = Cells(X, 1).Value
Rows(X & ":" & X).Select
Selection.Delete Shift:=xlUp
GoTo SkipIncrement
End If
X = X + 1
SkipIncrement:
Loop
If Fnd 0 Then
Sheets("Sheet2").Select
Range("A65000").End(xlUp).Select 'this is a row with data, this row +1
is empty!
X = ActiveCell.Row
For Y = 1 To Fnd
X = X + 1
Cells(X, 1).Value = DataArray(Y, 1)
Next
End If


End Sub