Delete Filtered Rows In a Table (Excel 2007+)

[Wookiee]

Hello, my fellow Excel enthusiasts. I have a problem which I know is not insurmountable, but whose solution I have so far failed to find online anywhere. After spending almost an hour searching blogs and forums, I felt it best to pose my question here.

I have a macro which opens an existing file, then deletes unnecessary columns and leaves me with a 3-column table. The problem I have is that my usual code for autofilter/delete hangs up when I attempt to delete the visible rows after having set the filter for my criteria. I know this has something to do with the fact that my data's in a table, but I haven't figured out a way so far to allow me to delete those useless rows of data. I'm sure I could construct a loop to do it, but I try to avoid loops in my macros whenever possible.

Ironically, the code I got by using the macro recorder doesn't even work. Here is the pertinent code (VBA throws the error on the last line; informing me that I'm attempting to shift cells on a table in my worksheet).

ActiveSheet.ListObjects("tblClerks").Range.AutoFilter Field:=3, Criteria1:="<>Vendor"
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete

Can anyone offer a solution?

Thanks!

 

[Dryver14]

Try this for the last line

Selection.SpecialCells(xlCellTypeVisible).EntireRow.Delete

 

[Andrew Poulsom]

This worked for me:

Sub Test()
    Dim Rng As Range
    With ActiveSheet.ListObjects("tblClerks").Range
        .AutoFilter Field:=3, Criteria1:="<>Vendor"
        Set Rng = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
        .AutoFilter
        Rng.Delete
    End With
End Sub

It seems you need to remove the filter.

 

[Wookiee]

Try this for the last line

Selection.SpecialCells(xlCellTypeVisible).EntireRow.Delete

Thanks for the suggestion, Dryver, but I'm afraid that doesn't work for me. I get a slightly differently worded error (1004: Delete method of Range class failed), but the same result.

 

[Andrew Poulsom]

Did yu try the code I posted?

 

[Wookiee]

It seems you need to remove the filter.

Thank you very much, Andrew. That did the trick.

I'll admit I'd seen some references to resizing and offsetting in other forums, but nothing that appeared to fit my particular situation. I'm going to have to sit down and really pick apart that line of code so I can better understand it, but for the nonce, the solution works and that suits me just fine.

 

[JoeMo]

Here's an alternative:

Sub DeleteFilteredRows()
ActiveSheet.ListObjects("tblClerks").Range.AutoFilter Field:=3, Criteria1:="<>Vendor"
ActiveSheet.ListObjects("tblClerks").DataBodyRange.EntireRow.Delete
'comment out next line if you don't want filter removed after deletion
ActiveSheet.ListObjects("tblClerks").Range.AutoFilter Field:=3
End Sub

 

[Dryver14]

Andrew,

I am impressed that you remove the filter prior to deletion, would never ever have crossed my mind,

Did you do that to keep it within the "with"

 

[Andrew Poulsom]

The removal of the filter needs to be inside the With ... End With but not the Delete.

 

[Wookiee]

Here's an alternative:

Sub DeleteFilteredRows()
ActiveSheet.ListObjects("tblClerks").Range.AutoFilter Field:=3, Criteria1:="<>Vendor"
ActiveSheet.ListObjects("tblClerks").DataBodyRange.EntireRow.Delete
'comment out next line if you don't want filter removed after deletion
ActiveSheet.ListObjects("tblClerks").Range.AutoFilter Field:=3
End Sub

Thanks, Joe. I'm afraid that code still gave me the error, but I did appreciate the fact that it introduced me to the DataBodyRange property. What I ultimately ended up doing was to use the DataBodyRange from your code and integrate it with the code provided by Andrew to create a simple subroutine to delete filtered rows (because my macro actually utilizes filter/delete a couple of times on that table).

Here's the code:

Dim rngDelete As Range

With ActiveSheet.ListObjects("tblClerks").DataBodyRange
    Set rngDelete = .SpecialCells(xlCellTypeVisible)
    .AutoFilter
End With

rngDelete.Delete
Set rngDelete = Nothing

I thank you both for your assistance. I really appreciate this forum and all those who take their time to help out with advice, code, and explanations.