Filter and delete rows on multiple criteria

KGee

Well-known Member
Joined
Nov 26, 2008
Messages
526
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

ask2tsp

Well-known Member
Joined
Feb 18, 2015
Messages
506
Office Version
  1. 365
Platform
  1. Windows
My implementation. Pick / change the parts you like.

Note: You must set reference to Microsoft Scripting Runtime
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,087
Members
414,501
Latest member
mdhaumyu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top