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

CodePudding user response：

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