Run Time Error 1004 (Delete Method of Range Object failed) by deleting filtered rows-CodePudding

I want to filter my Excel table by two criterias and delete the filtered rows. I wrote the following code:

With ThisWorkbook.Worksheets("Table1").ListObjects("coordinates").Range 
    .AutoFilter Field:=3, Criteria1:=longitude_arr, Operator:=xlFilterValues
    .AutoFilter Field:=4, Criteria1:=latitude_arr, Operator:=xlFilterValues
    .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With

The filtering works fine, but the delete part gets me a runtime error 1004: Delete Method of Range Object failed. I don't see the problem, can you help me?

CodePudding user response：

I assume the error is thrown because you try to delete rows from a table, but you have also one row that is not part of the table: The Offset(1, 0) will add the first row after the table to the rows to be deleted.

Try the following code - it will remove this extra row before deletion. Also, I added a check if anything is to be deleted, if not, skip the delete-command (else you would get a runtime error)

Dim sourceRange As Range
Set sourceRange = ThisWorkbook.Worksheets("Table1").ListObjects("coordinates").Range
With sourceRange
    .AutoFilter Field:=3, Criteria1:=longitude_arr, Operator:=xlFilterValues
    .AutoFilter Field:=4, Criteria1:=latitude_arr, Operator:=xlFilterValues        Dim filterRange As Range
    Set filterRange = .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow
    Dim deleteRange as Range
    Set deleteRange = Intersect(filterRange, sourceRange)
    if not deleteRange is Nothing then deleteRange.Delete
End With

Update Obviously I made a mistake when copying my test code by keeping my Autofilter-command, replaced it with the one from the quesion.

Playing around revealed some details:
o The Delete will also fail because of the EntireRow.
o I was not aware that there is a DataBodyRange-Object for a ListObject, thats makes it much easier.
o I also forgot that .SpecialCells(xlCellTypeVisible) throws a runtime error rather than returning Nothing, so you need to enclose this with On Error.
o If you want to get rid of the question if you want to delete the entire row, use Application.DisplayAlerts = False

I now end up with

With ThisWorkbook.Worksheets(1).ListObjects(1).DataBodyRange
    .AutoFilter Field:=3, Criteria1:=longitude_arr, Operator:=xlFilterValues
    .AutoFilter Field:=4, Criteria1:=latitude_arr, Operator:=xlFilterValues
    Dim filterRange As Range
    On Error Resume Next
    Set filterRange = .SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    Application.DisplayAlerts = False
    If Not filterRange Is Nothing Then filterRange.Delete
    Application.DisplayAlerts = True

End With