Delete Filtered Rows in Table and not entire Row

Question

How do I delete table rows only and not entire worksheet rows when my table is filtered?

Dim TradeTable As Excel.ListObject
Set TradeTable = Sheets("Pre Trade").ListObjects("PreTradeTable")

On Error Resume Next
        With TradeTable
        TradeTable.Select
            .Range.AutoFilter Field:=.ListColumns("Ask Spread").Index, Criteria1:=""
            Call .DataBodyRange.SpecialCells(xlCellTypeVisible).Select
            Selection.Delete
            .Range.AutoFilter
        End With
    On Error GoTo 0

@BigBen: The suggested answer deletes entire rows. `DisplayAlerts` is used for suppressing the dialog that asks you if you want to delete entire rows. — VBasic2008, Dec 02 '21 at 22:33

VBasic2008 · Accepted Answer · 2021-12-02T23:19:52.473

Delete Table Rows (Not Entire Rows)

Option Explicit

Sub DeleteTableRows()
    
    Dim wb As Workbook: Set wb = ThisWorkbook
    
    Dim TradeTable As ListObject
    Set TradeTable = wb.Worksheets("Pre Trade").ListObjects("PreTradeTable")
    
    With TradeTable
        
        .AutoFilter.ShowAllData ' clear possible previous filter
        
        .Range.AutoFilter Field:=.ListColumns("Ask Spread").Index, Criteria1:=""
        
        Dim vrg As Range ' Visible Cells Range
        On Error Resume Next ' prevent error if no filtered rows
            Set vrg = .DataBodyRange.SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        
        .AutoFilter.ShowAllData ' this does the trick
        
        If Not vrg Is Nothing Then
            vrg.Delete Shift:=xlShiftUp
        End If
    
    End With

End Sub

Says option variable or with block not set – Motacular Dec 03 '21 at 15:51 — Motacular, Dec 03 '21 at 15:51
I got it to work! Thank you – Motacular Dec 03 '21 at 15:57 — Motacular, Dec 03 '21 at 15:57

Delete Filtered Rows in Table and not entire Row

1 Answers1

Delete Table Rows (Not Entire Rows)