Filter and delete rows on multiple criteria

[KGee]

I need to open a comma delimited file from a network share and delete entire rows of data if the fourth delimited value in the row matches one of my keywords. There are 150 different options for that field and I need to exclude 38 of them. I am hoping this is something that can be automated using VBA.

The first two rows in the file contain detail that needs to remain intact and the third row contains comma delimited row headers which should also remain intact. The data I need to review begins in the fourth row and varies each month between 30-40k lines. I need to save the edited file in the same format with a different file name and leave the original file intact. I am only concerned with the code to filter and delete the rows meeting the criteria. I should have code to open and save the file once the editing is complete.

 

[ask2tsp]

My implementation. Pick / change the parts you like.

Note: You must set reference to Microsoft Scripting Runtime

Option Explicit

Sub deleteUnwanted()
    Static fso  As FileSystemObject
    
    Dim topFolder   As Scripting.folder
    Dim csvFile     As Scripting.File
    Dim ts          As Scripting.TextStream
    Dim tsOut       As Scripting.TextStream
    Dim outName     As String
    Dim csvLine     As String
    Dim csvItems()  As String
    Dim exclude     As String
    Dim r           As Long
    
    If fso Is Nothing Then Set fso = New FileSystemObject
    
    '----- get labels to exclude from sheet1 (codeName sht1)
    r = 2: exclude = ","
    While sht1.Cells(r, 1) > ""
        exclude = exclude & sht1.Cells(r, 1) & ","
        r = r + 1
    Wend
    
    '<-- edit next line to suit your needs
    Set topFolder = fso.GetFolder(ThisWorkbook.Path)
    
    For Each csvFile In topFolder.Files
      With csvFile
        If .Name Like "*.csv" Then
            Set ts = .OpenAsTextStream
            outName = .ParentFolder.Path & "\" & _
                Left(.Name, InStrRev(.Name, ".") - 1) _
                  & "_filtered.csv"
            Set tsOut = fso.CreateTextFile(outName, True)
            '-----------------------
            'this is the filter part
            'note delete if in exclude list is implemented as
            '     write if not in exclude list
            r = 0 'record counter (first 3 recs must always copy)
            Do Until ts.AtEndOfStream
                csvLine = ts.ReadLine: r = r + 1
                csvItems = Split(csvLine, ",")
                If InStr(1, exclude, "," & csvItems(3) & ",") = 0 _
                Or r < 4 Then tsOut.WriteLine csvLine
            Loop
            '-----------------------
            
            ts.Close
            tsOut.Close
        End If
      End With
    Next csvFile
End Sub