Delete rows that contains certain text - fastest way

[saulodefaria]

Hi guys!

Here's my problem: I have some huge txt files (~700k lines) with csv data from stock prices. One of the columns is the name of the company. So I want to get only the lines of certain companies. I actually already have a solution, but it is taking too much time. I think it may take hours to filter just one txt file, but I have lots of them...

Here's the code I'm using now:

Sub delete()


Dim dontDelete
dontDelete = Array("APPLE", "MICROSOFT", "GOOGLE")


Dim i As Long, j As Long


Dim isThere As Boolean


For i = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
    For j = LBound(dontDelete) To UBound(dontDelete)
        If InStr(1, Range("A" & i), dontDelete(j)) Then
            isThere = True
        End If
    Next j
    If Not isThere Then
        Range("A" & i).delete shift:=xlUp
    End If
    isThere = False
Next i


End Sub

It does the job. But I was hoping I could get some ideas to do it faster.

Thanks!

 

[hiker95]

saulodefaria,

actually there are like 20 companies that I want to keep (not only 3), so is there a smarter way to put all the company names as constraint (maybe in an array) instead of doing ---InStr(oa(i, 1), "companyX") Or--- to all companies?

It is always best to display your actual raw data worksheet(s), and, the results that you are looking for. This way we can usually find a solution on the first go.

And, we should not have to create sample data.

I would have to see your actual raw data workbook/worksheet, with a separate worksheet with the list of like 20 companies.

You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.