"Delete method of range class failed" when trying to delete filtered rows

Question

I want to filter the table, then delete the rows that are visible, leaving only the rows that were not filtered in an essentially "new" table.

Sub DeleteRows()

    Dim ws As Worksheet
    Set ws = Sheets("Score")

    Set TestTable = ws.ListObjects("Score")
    With ws
        .Range(TestTable & "[Correct/Incorrect]").AutoFilter 6, "Correct"
        .Range(TestTable & "[Correct/Incorrect]").Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilterMode = False
    End With
End Sub

Relevant: [How to delete filtered rows](https://stackoverflow.com/questions/17194394/vba-how-to-delete-filtered-rows-in-excel) — Christofer Weber, Sep 14 '22 at 08:06
I followed the instructions on this page and did not have any different results. Still showing the same error on the same line. — ATCEO, Sep 14 '22 at 08:22
What error on what line? Is deleting the rows the only problem, or is there something else? — Christofer Weber, Sep 14 '22 at 08:33

Ike · Answer 1 · 2022-09-14T09:14:05.720

You can use this code:

Sub deleteFilterdRows()

Dim ws As Worksheet
Set ws = Sheets("Score")

Dim lo as ListObject
Set lo = ws.ListObjects("Score")

lo.DataBodyRange.AutoFilter field:=lo.ListColumns("Correct/Incorrect").Index, Criteria1:="Correct"

Dim rgFiltered As Range
On Error Resume Next    'if nothing was found/filtered
With lo.Range
   Set rgFiltered = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
End With
On Error GoTo 0

lo.AutoFilter.ShowAllData 'remove filter otherwise deletion is not possible

If Not rgFiltered Is Nothing Then
    rgFiltered.Delete
End If


End Sub

First of all: you set the autofilter in a wrong way: .Range(TestTable & "[Correct/Incorrect]") won't work as you are combining the listobject with a string.

Second you need to apply the visible rows to an extra range and undo the filter. Within a filtered listobject you can't delete rows (same in frontend).

score 0 · Answer 2 · answered Sep 15 '22 at 22:33

Delete Excel Table Rows (ListObject)

Option Explicit

Sub DeleteListObjectRows()
        
    ' Define constants.
    Const wsName As String = "Score"
    Const tblName As String = "Score"
    Const lcName As String = "Correct/Incorrect"
    Const Criteria As String = "Correct"
        
    ' Reference the workbook, worksheet and table.
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets(wsName)
    Dim tbl As ListObject: Set tbl = ws.ListObjects(tblName)
    
    Application.ScreenUpdating = False
    
    Dim vrg As Range
    
    With tbl
        
        ' Check if the autofilter arrows are turned on.
        If .ShowAutoFilter Then ' autofilter arrows are turned on
            ' Clear any table filters.
            If .AutoFilter.FilterMode Then .AutoFilter.ShowAllData
        Else ' autofilter arrows are turned off
            .ShowAutoFilter = True ' turn on the autofilter arrows
        End If
        
        ' Reference the criteria list column ('lc').
        Dim lc As ListColumn: Set lc = .ListColumns(lcName)
        ' Filter the table range.
        .Range.AutoFilter lc.Index, Criteria
        
        ' Attempt to reference the visible range ('vrg').
        On Error Resume Next
            Set vrg = .DataBodyRange.SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        
        ' Clear the filter.
        .AutoFilter.ShowAllData
    
    End With
    
    ' Validate and delete the visible range.
    If Not vrg Is Nothing Then vrg.Delete xlShiftUp

    Application.ScreenUpdating = True
    
    ' Inform.
    If Not vrg Is Nothing Then
        MsgBox "Table criteria rows deleted.", vbInformation
    Else
        MsgBox "No table criteria rows found.", vbExclamation
    End If
    
End Sub

"Delete method of range class failed" when trying to delete filtered rows

2 Answers2

Delete Excel Table Rows (ListObject)