Deleting filtered Rows

[madvogue29]

Hi I have used this code on a sheet and it was working before but now it is giving me error 1004

Dim SelectedCell, Cell, rng As Range
Dim tbl As ListObject
Dim x As Long, i, y As Long
Dim hdr_Row As Long, last_row As Long, lastdata_row As Long

Worksheets("meetingTemplate").Activate
For Each tbl In ActiveSheet.ListObjects
Set rng = tbl.ListColumns(3).Range


For Each Cell In rng
If Not Cell.value = "COMPLETED" Then
Else
Application.DisplayAlerts = False
tbl.Range.AutoFilter 3, "COMPLETED"
    Range(tbl.Name).Select
    Selection.EntireRow.Delete
    Application.DisplayAlerts = True
ActiveSheet.ShowAllData
End If

I am getting error in the Selection.EntireRow.Delete

I can see the table being filtered and the rows selected but it just wont delete.

I hope you can help me

 

[Yongle]

What else does the error mrssage say?

 

[madvogue29]

I had Delete method of range class failed or soemthing along these lines.

I did a work around for now but its not the most efficient

Worksheets("meetingTemplate").Activate
Set tbl = ActiveSheet.ListObjects(2)
Set rng = tbl.ListColumns(3).Range
lastdata_row = tbl.DataBodyRange.Cells(tbl.ListRows.Count, 3).End(xlUp).Row
'MsgBox lastdata_row

For x = lastdata_row To 5 Step -1


If Range("D" & x).value = "COMPLETED" Then
Range("D" & x).Select
Selection.ListObject.ListRows(x - 4).Delete
End If
lastdata_row = lastdata_row - 1
Next x
For Each Cell In rng
If Not Cell.value = "COMPLETED" Then
Else
Cell.Select
x = Selection.Row - Selection.ListObject.Range.Row
'
'MsgBox x
End If

This way the macros searches from the bottom of the table up and deletes the list object row if it meets the criteria

 

[Yongle]

Are you trying to delete every row in the table where the cell in 3rd column in table = "COMPLETED" ?

Sub DeleteCompleted()
    Application.DisplayAlerts = False
    With Worksheets("meetingTemplate").ListObjects(2)
        .AutoFilter.ShowAllData
        .Range.AutoFilter Field:=3, Criteria1:="COMPLETED"
        On Error Resume Next
        .ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
        On Error GoTo 0
        .AutoFilter.ShowAllData
    End With
    Application.DisplayAlerts = True
End Sub