Link to home
Start Free TrialLog in
Avatar of bsharath
bsharathFlag for India

asked on

Find all data from text file and delete the whole row which is not found

Hi,

I want a macro to search all the machine names which i have in a txt file and cut paste anything else which is not there in the sheet to a different sheet.
In the text file i have
Machine1
Machine2
In the excel i have
Machine1
machine2
Machine3
What i need is machine 3 should be cut pasted to a new sheet

Regards
Sharath
Avatar of Hitesh Manglani
Hitesh Manglani
Flag of India image

Sub Macro1()
Dim FF As Integer, str1 As String, j as Integer, idfound as Boolean
s2row=1
j=1
FF = FreeFile
Open "C:\filename.txt" For Input As #FF
For i = 1 To Sheet1.UsedRange.Rows.Count
      idfound = false
       While Not EOF(FF)
           Line Input #FF, str1
           If  UCase(str1) = UCase(Sheet1.Cells(i, j)) And Sheet1.Cells(i, j) <> "" Then
                idfound = true
            End If
        Wend
       if idfound = false then
             Sheet1.Rows(i).Cut Destination=Sheet2.Rows(s2row)
            s2row=s2row+1
       End if
 Next
Close FF
End Sub
Avatar of bsharath

ASKER

I get this.

Run time error '13'
ASKER CERTIFIED SOLUTION
Avatar of Hitesh Manglani
Hitesh Manglani
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hitesh small dout in this.What will this macro do.

Where should i have the search data in the excel.Which colum