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
LVL 11
bsharathAsked:
Who is Participating?
 
Hitesh ManglaniCommented:
Sub Macro1()
Dim FF As Integer, str1 As String, j As Integer, idfound As Boolean
s2row = 1
j = 1
For i = 1 To Sheet1.UsedRange.Rows.Count
      idfound = False
      FF = FreeFile
      Open "C:\filename.txt" For Input As #FF

       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
        Close FF
       If idfound = False Then
             Sheet1.Rows(i).Cut Destination:=Sheet2.Rows(s2row)
            s2row = s2row + 1
       End If
 Next

End Sub


0
 
Hitesh ManglaniCommented:
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
0
 
bsharathAuthor Commented:
I get this.

Run time error '13'
0
 
bsharathAuthor Commented:
Hitesh small dout in this.What will this macro do.

Where should i have the search data in the excel.Which colum
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.