excelvbaautofilterexcel-tableslistobject

Delete Filtered Rows in Table and not entire Row


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

Solution

  • 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