"Delete method of range class failed" when trying to delete filtered rows via the below method. What is going wrong?

ATCEO 0
Viewed 30

I've been at this for an hour already. I simply want to filter the table, then delete the rows that have been filtered and are visible, leaving only the rows that were not filtered in an essentially "new" table.


    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

I've tried multiple methods and googling, and nothing is coming close to what I need.

vba excel
2 Answers

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).

useryPy710

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
VBasic20080
Related