Advancedfilter Delete Row(s)

[brorick]

I would like to use advancedfilter to delete every record in a range based on my criteria. Here is an example

Located on Sheet1 and the range is titled ProdList.
id item cost
1001 pie 5.75
1002 cake 8.00
1003 pie 7.75


Located on Sheet2 and the range is titled DelCrit. This is what should be deleted from Sheet1.

id item cost
1001 pie 5.75
1003 pie 7.75

Here is my attempt to create the code. Unfortunately it is not working as I had hoped. This code is deleting the row and the header. I am not sure what is missing in my code.

Private Sub btnDeleteFromList_Click()
Dim delRng As Range
Dim FindText As Range
Set FindText = Worksheets("Invoice").Range("ProdList")
Set delRng = Worksheets("frmAdmin").Range("DelCrit")
FindText.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("delRng")
FindText.SpecialCells(xlCellTypeVisible).EntireRow.Delete
Worksheets("Invoice").ShowAllData
End Sub

Does anyone have a working example? I haven't been able to find a very good example anywhere on the internet. Any thoughts? :help

[lucas]

Hi brorick,
Since you didn't post a workbook with your data and ranges I will offer a different method......as I was posting I realized that you have to run it twice for some reason to get all of the dups......will check into it later...don't have time right now but it will maybe give you an idea or two.....

[mikerickson]

This should work if ProdList and DelCrit include the headers
With Range("ProdList")
.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("DelCrit"), Unique:=False
On Error Resume Next
.Offset(1, 0).SpecialCells(xlCellTypeVisible).Delete shift:=xlUp
.Parent.ShowAllData
On Error GoTo 0
End With

[brorick]

Lucas and Mikerickson thank you both for your help. I will give them both a try.

[brorick]

:beerchug: Solved.

[lucas]

Please post your final solution for the benifit of others....


You can mark your own thread solved using the thread tools at the top of the page..