brorick
06-12-2008, 07:36 PM
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
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