Help with Autofilter macro

[nirvehex]

Hey guys,

This seems easy enough, but I can't quite get it.

Here's the code:

Sub deletelines()
'
' deletelines Macro


    ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=1, Criteria1:= _
        "DELETE"
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    Range("Table2[[#Headers],[LineStatus]]").Select
    Selection.AutoFilter
End Sub

All I want to do is filter in column A for any line that says "DELETE" and select all the rows that do and delete them. Then unfilter.

Any ideas how to do this?

Thanks.

 

[AOB]

Dim sht as Worksheet
Dim rng As Range
Dim rngFiltered As Range

Set sht = ActiveSheet

With sht

    Set rng = .ListObjects("Table2").Range

    .AutoFilterMode = False

    With rng

        .AutoFilter Field:=1, Criteria1:="=DELETE"

        Set rngFiltered = .SpecialCells(xlCellTypeVisible)

        If rngFiltered.Rows.Count > 1 Then

            .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Delete

        End If

    End With

    .AutoFilterMode = False

End With

 

[scott_n_phnx]

Another option to @AOB, is one that I cam across that is faster than turning off autofilter, finding and deleting and then turning autofilter back on. It is a lot of code, but is really quick.

Sub DeleteDELETE()

    Dim Firstrow As Long
    Dim LastRow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long
    Dim colDEL As Integer

        colDEL = 1 'Column Number
    
    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    With ActiveSheet

        .Select

        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView

        .DisplayPageBreaks = False

        Firstrow = .UsedRange.Cells(2).Row
        LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

        For Lrow = LastRow To Firstrow Step -1

            With .Cells(Lrow, colDEL)

                If Not IsError(.Value) Then

                    If .Value = "DELETE" Then .EntireRow.Delete 'Looks for DELETE and deletes entire row

                End If

            End With

        Next Lrow

    End With

    ActiveWindow.View = ViewMode
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With

End Sub