Sorting and Deleting VBA for large file

[heathball]

Eventually settled on this vba recording, as it was the best result i could achieve when looking for this goal-
Delete rows that have TRUE in column B.

806,352 rows, 160 columns. ("A:FD")
Anything with filter/delete was horrible. A "delete row if cell matches TRUE" did not work at all.

This method below is what one would do on excel without VBA, for easy deletion, and is not much quicker.
(sort the true/false, so the TRUE can be deleted seperately)

But i thought i would try to find out if Google is keeping something from me, as i need to do this quite often in the near future.
Is there a way to use some combination of FILTERS/SORT/DELETE, that results in something smoother than excel functions?

The sort column "B" only has TRUE/FALSE >> does that mean a relatively simple ARRAY code could help?

Thanks in advance.

  ActiveWorkbook.Worksheets("sheet5").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("sheet5").AutoFilter.Sort.SortFields.Add2 Key:= _
        Range("b:b"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("sheet5").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
   
  Columns("B:B").Select
 Selection.Find(What:="true", after:=ActiveCell, LookIn:=xlFormulas2, _
       LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
       MatchCase:=False, SearchFormat:=False).Activate

ActiveCell.EntireRow.Select
  Range(Selection, Selection.End(xlDown)).Delete

 

[Phuoc]

You can delete these 3 lines:

Dim t
t = Timer
MsgBox Timer - t

It is used to know the running time of the code

 

[heathball]

i ran the code, it was not moving after several minutes, then when i "escaped", the debug box appeared for this "end if"

 

[Alex Blakenburg]

i ran the code, it was not moving after several minutes, then when i "escaped", the debug box appeared for this "end if"

Deleting any reasonable percentage of 800k rows using non-contiguous rows is bound to be slow.
Sorting them so that you delete a single contiguous block is bound to be faster.

Using an array as I have in post #9 which does not require any rows to be deleted has the potential to be faster again but you would need to try it on your data.

 

[heathball]

Based on the above, this should be faster.
Note: I have assumed a heading row in row 1 and the data starting at row 2

Sub RemoveRowsWithTrue()
    Dim ws As Worksheet
    Dim rng As Range
    Dim lastRow As Long
    Dim arrOrig As Variant, arrNew As Variant
    Dim i As Long, j As Long, iNew As Long
   
    Set ws = Worksheets("Sheet5")           ' <--- Change as required
    With ws
        lastRow = .Range("B" & Rows.Count).End(xlUp).Row
        Set rng = .Range(.Cells(2, "A"), .Cells(lastRow, "FD"))
        arrOrig = rng.Value2
    End With
   
    ReDim arrNew(1 To UBound(arrOrig, 1), 1 To UBound(arrOrig, 2))
   
    For i = 1 To UBound(arrOrig)
        If StrComp(arrOrig(i, 2), True, vbTextCompare) <> 0 Then
            iNew = iNew + 1
            For j = 1 To UBound(arrOrig, 2)
                arrNew(iNew, j) = arrOrig(i, j)
            Next j
        End If
    Next i

    rng.ClearContents
    rng.Resize(iNew, UBound(arrNew, 2)).Value2 = arrNew
   
    ' Reset Used Range
    Dim tmp As String
    tmp = ws.UsedRange.Address
End Sub

This looks like an exciting piece of code!
a strange thing happened, which may relate to a row limit within excel which i have noted previously.
I am left with 82,436 rows on the file. ALL false in column B.
there should be half of the original (which is approx 403,000), as half of column B is true, half is false.

 

[Alex Blakenburg]

I can't replicate your issue but it does look like an array with that many cells can't beat the sort and delete method.
(Cells approx 800k rows * 160 columns = 128M)

The below uses @RobP's sort and removes the Select parts from the delete process:

    Dim ws As Worksheet
    Dim rg As Range
    Dim row_search As Long
    Dim lastRow As Long
 
    Application.ScreenUpdating = False
    Set ws = Worksheets("Sheet5")
    With ws
        lastRow = .Cells(Rows.Count, 2).End(xlUp).Row
        Set rg = .Range("A2:FD" & lastRow) ' Assumed your data starts in row 2 with a heading in row 1
        rg.Sort rg.Columns("B"), xlAscending
        
        row_search = .Columns("B:B").Find(What:="true", LookIn:=xlValues, _
           LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
           MatchCase:=False, SearchFormat:=False).Row
        .Range(.Cells(row_search, "B"), .Cells(lastRow, "B")).EntireRow.Delete
    End With
    Application.ScreenUpdating = True
End Sub

 

[Alex Blakenburg]

If your data is coming to you as a csv / text file. You may be able to save some time by using Power Query to load in just the records you want right from the start.

 

[heathball]

Thanks to everyone for assisting with that. It was a great help!
Alex, thanks for your PQ suggestion. At the moment i work with no incoming files.
Cheers