Efficient method to sort read-in text file in Excel

Question

I've read in a text file to excel from a database and I've done it in such a way that it filters out unnecessary columns. My approach to filter rows was to use two subroutines and call the 2nd from within the first. It takes ~8 seconds for the sheet to be filtered and there is only 400 or so rows. The fact that it takes that long (even though it works) indicates my code is inefficient. If anyone has a better method I would greatly appreciate the knowledge! To delete rows I've used the following VBA:

Sub FilterAndDelete()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
Select Case Left(Range("A" & i).Value, 3)
Case "CHA", "HAM", "BKN"
    Call FilterAndDeleteB
Case Else
    Rows(i).Delete
    Call FilterAndDeleteB
End Select
Next i
End Sub

Sub FilterAndDeleteB()
Dim Br As Long, i As Long
Br = Range("B" & Rows.Count).End(xlUp).Row
For i = Br To 1 Step -1
Select Case Left(Range("B" & i).Value, 1)
Case "-"
    Rows(i).Delete
Case Else
    'do nothing
End Select
Next i

Answer 1

I'm confused here—you are looping through FilterAndDeleteB for every row with a value in column A? Let's look at the logic:

Count rows for column A
    From last row of column A to the first row of column A
        If first 3 characters of A cell are `CHA` `HAM` or `BKN`
            count rows in column B
                for last row of column B to the first row of column B
                    If B cell starts with `-` then delete current row
        Else            
            delete current row
                count rows in column B
                for last row of column B to the first row of column B
                    If B cell starts with `-` then delete

Next row in Column A

Right, so for every single row in Column A, you will loop through every single row in column B. Once you loop through column B, you should never need to loop through it again, according to the logic. Everything beginning with - will already have been deleted.

Instead, I imagine, you'd rather look for CHA, HAM, and BKN, and for every row where one of those isn't there, if there's a - in column B, delete those rows—right? If so, consider this (untested) code:

Option Explicit

Public Sub FilterAndDeleteRows()
    Const KEEP_STRING As String = "CHA,HAM,BKN"
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Dim i As Long
    Dim beginningString As String
    For i = lastRow To 1 Step -1
        beginningString = Left(Cells(i, 1), 3)
        If NOT InStr(1, KEEP_STRING, beginningString) > 0 Then
            If Left(Cells(i, 2), 1) = "-" Then Rows(i).Delete
        End If
    Next i
End Sub

Now it's only one loop, and it's skipping the rows where A matches.

---

But the more likely place to improve the speed of your filtering is by filtering when getting data from the file. At that point, you probably have the data in an array and you probably could just move records from that array that match your criteria to a new array, which you print out at the end.

Answer 2

1. Are you reading the data from the database via SQL? Why not include only the columns you want in the SQL, and add a WHERE clause to the SQL to filter the rows?

2. You might also be able to use ADODB to connect to the text file itself, and once again, issue an SQL statement which will return only the desired rows and columns. Excel has a CopyFromRecordset method to paste results in an ADO Recordset to an Excel worksheet.