Visual Studio Code – Script too slow. How to handle big files?

There are 2 things in your code that makes the execution slow.

The first thing has something to do with Excel vs VBA. Every time your VBA code needs something from Excel, it has to call an internal interface and that is rather slow. You can’t measure this when you have a sheet with a few rows/columns, but in a sheet with 400k rows and (at least) 13 columns of data, you have 5 millions cells, and your code reads most of them 2 times. This can be speed up by reading large chunks of data into an array. This is only one read and for that 5 million cells it’s a matter of maybe a second.

The second thing is pure Excel: Deleting a row of data from a worksheet is painfully slow, even if you switch off recalculation and screen update. That means, you should decrease the number of deletes by "collecting" rows to be deleted into a Range variable and then delete them all at once. However, the number of rows collected shouldn’t bee too high. I experimented a little bit and 1000 seemed to be reasonable.

Sub DeleteLastRows()
    Const DeleteChunkSize = 1000
    
    Dim lastRow As Long
    With ThisWorkbook.Sheets(1)
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).row
    
        ' Read all Data into Memory
        Dim AllData As Variant
        AllData = .Range(.Cells(1, 1), .Cells(lastRow, 13))
        Debug.Print "data read"
        Dim row As Long
        For row = lastRow To 2 Step -1
            If row Mod 100 = 0 Then DoEvents
            Dim deleteRange As Range, deleteCount As Long
            
            Dim toBeDeleted As Boolean
            toBeDeleted = ThatSpecialLine(AllData, "0", row, 1, 9) Or EmptyRow(AllData, row, 1, 13)
            If toBeDeleted Then
                deleteCount = deleteCount + 1
                If deleteRange Is Nothing Then
                    Set deleteRange = .Cells(row, 1).EntireRow
                Else
                    Set deleteRange = Union(deleteRange, .Cells(row, 1).EntireRow)
                End If

                ' Delete only if a certain number of rows to be deleted is reached to speed up runtime
                If deleteCount >= DeleteChunkSize Then
                    DoEvents
                    deleteRange.Delete xlUp
                    Set deleteRange = Nothing
                    deleteCount = 0
                End If
            End If
        
        Next row
    End With
    ' delete the last chunk of data if any
    If Not deleteRange Is Nothing Then
        deleteRange.Delete xlUp
    End If

End Sub

I adapted your helper routine so that they work on the array of data which is passed as argument:

Function EmptyRow(data As Variant, row As Long, startc As Long, EndC As Long) As Boolean
    EmptyRow = True
    Dim temp As String
    
    Dim i As Long
    For i = startc To EndC
        temp = Trim(data(row, i))
        If temp <> "" Then
            EmptyRow = False
            Exit Function
        End If
    Next
End Function

Function ThatSpecialLine(data As Variant, val As String, row As Long, startc As Long, EndC As Long) As Boolean
    If Not EmptyRow(data, row, startc, EndC) Then Exit Function
    ThatSpecialLine = (data(row, EndC + 1) = val)
End Function

That code took more or less 1s for 1000 rows that where to be deleted – my example sheet had approx 30% of such rows. That would lead to a runtime in the range of few minutes.

But there is a much faster attempt, assuming that you are only interested in the data, not in formatting. Instead of deleting rows in the Excel sheet, copy the data you want to keep in a second array. When done, delete all data of your sheet and write the copied data back to Excel. This took maybe 2 or 3 seconds in my example sheet with > 800k rows:

Sub CopyRelevantData()
    Dim lastRow As Long
    With ThisWorkbook.Sheets(1)
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).row
    
        ' Read all Data into Memory
        Dim AllData As Variant, newData As Variant
        AllData = .Range(.Cells(1, 1), .Cells(lastRow, 13))
        ' Create a second array where you copy the data you want to keep
        ReDim newData(LBound(AllData, 1) To UBound(AllData, 1), LBound(AllData, 2) To UBound(AllData, 2))
        Debug.Print "data read"
        Dim row As Long, newRow As Long
        For row = 1 To lastRow
            Dim toBeDeleted As Boolean
            toBeDeleted = ThatSpecialLine(AllData, "0", row, 1, 9) Or EmptyRow(AllData, row, 1, 13)
            If Not toBeDeleted Then
                ' Copy this row of data
                newRow = newRow + 1
                Dim col As Long
                For col = LBound(AllData, 2) To UBound(AllData, 2)
                    newData(newRow, col) = AllData(row, col)
                Next col
            End If
            If row Mod 100 = 0 Then DoEvents
        Next row
        
        .UsedRange.Clear
        .Cells(1, 1).Resize(UBound(AllData, 1), UBound(AllData, 2)) = newData
    End With
End Sub