Delete Rows - as in remove them from the sheet completely?

Hi! I'm a Noob with VBA, but I've been trying to automate some processes at work. We have an excel file that contains some 70k records, which we download every day. However, we only need records that don't have a cancelled or completed status (Statuses are text in a column). When I manually autofilter to de-include those statuses, I end up with about 6k records of usable data, which is fine, but i wrote a macro to automatically filter the file for those cancelled or completed statuses, select the rows, and delete them. The end result looks about right, but the file takes MUCH longer to open and interact with than the 5k records could be responsible for. Also, when I ask the Immediate window in VBA for a Used Range count, it still includes the deleted records. I've enclosed my code and I hope I'm clear about the trouble.

(The Rows still show Blue after the Macro runs, but if I manually Select and RightClick->Delete the rows in question, they go away permanent-like.)

Sub NewQDeleteCompletedCancelled()
    Dim NewQSheet As Worksheet
    Dim DeleteValue1, DeleteValue2 As String
    Dim rng As Range
    Dim calcmode As Long
    Workbooks.Open Filename:="U:\Test\NewQ.xls"
    With Application
        calcmode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
    DeleteValue1 = "Cancelled"
    DeleteValue2 = "Completed"
    With ActiveSheet
        .AutoFilterMode = False
        'Range is the column where the Status of the record lives
        .Range("V1:V" & .Rows.Count).AutoFilter Field:=1, _
        Criteria1:=DeleteValue1, Operator:=xlOr, Criteria2:=DeleteValue2
        With .AutoFilter.Range
            On Error Resume Next
            Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
                .SpecialCells(xlCellTypeVisible)
            On Error GoTo 0
            If Not rng Is Nothing Then rng.EntireRow.Delete
        End With
        .AutoFilterMode = False
    End With
    With Application
        .ScreenUpdating = True
        .Calculation = calcmode
        .DisplayAlerts = False
    End With
    ActiveWorkbook.Save
    Application.DisplayAlerts = True

End Sub

Any help you can provide would be simply neato! Thanks!

Hi, I did a little workaround based on what some co-workers said would be a good idea, which is just copy the range from the ss with data still in it into another sheet and then delete the old sheet. The code looks something like:

Sub NewQDeleteCompletedCancelled()
    Dim NewQSheet                   As Worksheet
    Dim DeleteValue1, DeleteValue2  As String
    Dim rng                         As Range
    Dim calcmode                    As Long
    
    
    Workbooks.Open Filename:="U:\Test\NewQ.xls" 'File name
    With Application 
        calcmode = .Calculation
        .Calculation = xlCalculationManual 'turn off Excel calculation (for increased performance)
        .ScreenUpdating = False 'Now the screen won't show what's happening (increased performance)
    End With
    DeleteValue1 = "Cancelled" 'first filter value to be deleted
    DeleteValue2 = "Completed" 'second filter value to be deleted
    With ActiveWorkbook.Worksheets("NewQ")
        Rows("1:4").Delete 'Header to the file with unnecessary stuff in it, data starts at Row 5
        .AutoFilterMode = False 'Disengage filter if it's on
        .Range("V1:V" & .Rows.Count).AutoFilter Field:=1, _
        Criteria1:=DeleteValue1, Operator:=xlOr, Criteria2:=DeleteValue2 'filter for statuses to be deleted
        With .AutoFilter.Range
            On Error Resume Next 'ignore if its broken
            Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _ 'add the row to the range if it's visible
                .SpecialCells(xlCellTypeVisible)
            On Error GoTo 0 'ignore broken
            If Not rng Is Nothing Then rng.EntireRow.Delete 'if not empty, kill it.
        End With
        .AutoFilterMode = False 'disengage filter
    End With
    Set NewQSheet = Sheets.Add 'build new worksheet
    With NewQSheet
        .Name = "NewQData"
        .Move ActiveWorkbook.Sheets(1)
    End With
    Sheets("NewQ").Select 'select old worksheet
    With ActiveWorkbook.Worksheets("NewQ")
        .Range("A1").Resize(Cells.Find(What:="*", SearchOrder:=xlRows, _
        SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
        Cells.Find(What:="*", SearchOrder:=xlByColumns, _
        SearchDirection:=xlPrevious, LookIn:=xlValues).Column).Select 'select only range with data, ignore empty.
    End With
    Selection.Copy
    Sheets("NewQData").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _ 'keep column width
        SkipBlanks:=False, Transpose:=False
    ActiveSheet.Paste 'paste.
    With Application 
        .ScreenUpdating = True 'Now you can see what Excel is doing.
        .Calculation = calcmode 'Excel can count again.
        .DisplayAlerts = False 'turn off fun warning boxes
    End With
    Sheets("NewQ").Delete 'kill sheet with empty rows
    With Sheets("NewQData")
        .Name = "NewQ" 'rename new sheet to old sheet name (for pivot macro I didn't feel like altering)
    End With
    ActiveWorkbook.Save '...save.
    Application.DisplayAlerts = True 'turn warning boxes back on

End Sub

So this one is Solved. Well, sorta It's a workaround, but it works.