Deleting filtered table rows

[ericdb]

Hello, I am struggling to figure out how to delete filtered table rows. In my file, I have reports directly connected to our report server and am trying to update my Macro to delete filtered rows. I have tried many different ways to do this and i keep coming up short. Below is an example of what keeps failing to work. I seem to always get hung up on the when i am trying to delete entire row. for some reason nothing is working for me, and i am not able to find anyone that has a solution that works. I think most of the solutions out there are for a range and not a table. At least that is what i can come up with at this time.

No matter how I write VBA it always seams to fail at the EntireRow.Delete line. I have tried this with a static range (Range("A2:K100000") and variable using a last row option. I have also tried using special cells type visible (SpecialCells(xlCellTypeVisible) and no luck. Any help would be greatly appreciated.

Example 1:
ActiveWindow.SmallScroll Down:=9
ActiveSheet.ListObjects("INV").Range.AutoFilter Field:=2, Criteria1:= _
"ATTIT"
ActiveSheet.ListObjects("INV").Range.AutoFilter Field:=4, Criteria1:= _
"SPARE"
Range("A2:K100000").Select
Selection.EntireRow.Delete
Range("C762").Select


Example 2:
If ActiveSheet.AutoFilterMode Then Selection.AutoFilter

ActiveCell.CurrentRegion.Select

With Selection
.AutoFilter
.AutoFilter Field:=2, Criteria1:="ATTIT-CRI"
.Offset(1, 0).Select
End With

Dim numRows As Long, numColumns As Long
numRows = Selection.Rows.Count
numColumns = Selection.Columns.Count

Selection.Resize(numRows - 1, numColumns).Select

With Selection

.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With

 

[John_w]

See if this works for you. It's based on your first example.

Public Sub Filter_and_Delete_Table_Rows()

    Dim visibleAreas As Range
    Dim visibleRows As Variant
    Dim i As Long
        
    Application.ScreenUpdating = False
    
    With ActiveSheet
        If Not .AutoFilterMode Then .UsedRange.AutoFilter

        .ListObjects("INV").Range.AutoFilter Field:=2, Criteria1:="ATTIT"
        .ListObjects("INV").Range.AutoFilter Field:=4, Criteria1:="SPARE"
        
        'Delete visible rows
        
        Set visibleAreas = .ListObjects("INV").Range.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow
        visibleRows = Split(visibleAreas.Address, ",")
        For i = UBound(visibleRows) To LBound(visibleRows) Step -1
            .Rows(visibleRows(i)).Delete
        Next
        
        'Show remaining rows
        
        If Not .AutoFilterMode Then .UsedRange.AutoFilter
    End With
    
    Application.ScreenUpdating = True
    
End Sub

 

[ericdb]

Thank you for the reply, but it did not work. It is failing again on the delete line. The error returned is: "Can't move Cells in a filtered range or table." Any other thoughts?

 

[John_w]

Try changing the Delete to EntireRow.Delete.

 

[ericdb]

Hello John_w, With that change I am still getting the same error.

 

[John_w]

I'm unable to reproduce the error. Can you upload your workbook so I can test the code?

 

[ericdb]

Hello John_w. I am not able to attach the file. My posting permissions say "You may not post Attachments". Not sure how to update the permissions to allow me to post the file.

 

[John_w]

You could upload it to a file sharing site and post the link.

 

[ericdb]

Hello John_w, I was able to get the Macro working. The issue was a hidden column causing the Macro to fail. Thanks for all your help!