how do we rewrite this program that it collects data from input.txt file with content

Question

How do we rewrite this program in order for it to collect data from input.txt file or second excel sheet with content:
test
mark
class ...
and delete rows in exel file with this text.

Sub RemoveRowsAv()
  ThisWorkbook.ActiveSheet.Cells.ClearFormats
Dim n As Long, d As Long
n = Range("G" & Rows.Count).End(xlUp).Row
For d = n To 1 Step -1
  If InStr(1, ThisWorkbook.ActiveSheet.Cells(d, 7).Text, "test", vbTextCompare) > 0 Then
    ThisWorkbook.ActiveSheet.Cells(d, 7).EntireRow.Delete
  End If
  If InStr(1, ThisWorkbook.ActiveSheet.Cells(d, 7).Text, "mark", vbTextCompare) > 0 Then
    ThisWorkbook.ActiveSheet.Cells(d, 7).EntireRow.Delete
  End If
Next d
End Sub

file input.txt

test
mark 
class

Does this answer your question? [Read/Parse text file line by line in VBA](https://stackoverflow.com/questions/11528694/read-parse-text-file-line-by-line-in-vba) — Geert Bellekens, Nov 20 '20 at 12:01

score 0 · Answer 1 · answered Nov 20 '20 at 10:22

0

You read the file and put every word of that file in a collection.
Then, instead of:

If InStr(..., "test", ...) Then
  ....EntireRow.Delete
End If

You do something like:

For Each entry in Collection
  If Instr(..., entry, ...) Then
    ....EntireRow.Delete
  End If
Next

answered Nov 20 '20 at 10:22

Dominique

16,450
15
56
112

how do we connect two program together Dim myFile As String, text As String, textline As String, posLat As Integer, posLong As Integer myFile = "C:\test\geographical-coordinates.txt" myFile = Application.GetOpenFilename() Open myFile For Input As #1 Do Until EOF(1) Line Input #1, textline text = text & textline Loop Close #1 posLat = InStr(text, "latitude") posLong = InStr(text, "longitude") Range("A1").Value = Mid(text, posLat + 10, 5) Range("A2").Value = Mid(text, posLong + 11, 5) – Prince Andrew Nov 20 '20 at 10:31

FaneDuru · Answer 2 · 2020-11-20T11:41:24.030

Test the next code, please. It needs a text file named "input.txt". In my example code it is located in the folder where ThisWorkbook has the path:

Sub RemoveRowsAv()
 Dim sh As Worksheet, n As Long, d As Long, arrCond, El, txtFileName As String
 Dim objFSO As Object, objTxt As Object, rngDel As range, strText As String

 Set sh = ThisWorkbook.ActiveSheet 'use here the necessary sheet (not necessary to be activated)
 sh.cells.ClearFormats
 txtFileName = ThisWorkbook.Path & "\" & "input.txt" 'fill here the text file full name
 
 Set objFSO = CreateObject("Scripting.FileSystemObject")
 If Not objFSO.FileExists(txtFileName) Then 'check if the text file exists in the path
    MsgBox "The text file does not exist on the path: """ & txtFileName & """."
    Exit Sub
 End If
 Set objTxt = objFSO.OpenTextFile(txtFileName, 1)
    strText = objTxt.ReadAll      'read the text file content
 objTxt.Close

 arrCond = Split(strText, vbCrLf) 'put it in an array splitting on vbCrLf (end of line)

 n = range("G" & rows.count).End(xlUp).row 'last row of the G:G column
 For d = 1 To n            'iterate between the existing range
    For Each El In arrCond 'check each element of the array keeping conditions
        If El <> "" Then
            If InStr(1, sh.cells(d, 7).text, El, vbTextCompare) > 0 Then
                If rngDel Is Nothing Then 'if the range to be deleted not Set
                    Set rngDel = sh.cells(d, 7)
                Else
                    Set rngDel = Union(rngDel, sh.cells(d, 7)) 'if already Set
                End If
            End If
        End If
    Next El
 Next d
 'delete all the rows at once:
 If Not rngDel Is Nothing Then rngDel.EntireRow.Delete xlUp
End Sub

how do we rewrite this program that it collects data from input.txt file with content

2 Answers2