modify Advanced Filter code to specify criteria range on different worksheet

Jaffey · Nov 25th 2012

I am trying to modify some code I found in another post to filter a list and then delete certain rows based on values in a range from a different worksheet instead of the current sheet. Here is the original code from the other post:

Code

Sub Filter_Delete()          
Dim lrow As Long
lrow = Cells(Rows.Count, 2).End(xlUp).Row


With Sheet1         
.Range("B5:B" & lrow).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _         
.Range("K1:K2"), Unique:= False
.Range("B6:B" & lrow).SpecialCells(xlCellTypeVisible).EntireRow.Delete         
.ShowAllData     
End With     
End Sub

Display More

As I understand it the line that specifies the range containing the criteria to be used for deleting the rows is:

Code

.Range("K1:K2"), Unique:=False

so I would like to change it to something like:

Code

.Sheets("RowDeleteCriteria").Range("A2:A100")

Any help is much appreciated!

Here a link to the original post: (http://www.ozgrid.com/forum/sh…ete+rows+based+criteriato)

John_w · Nov 26th 2012

Re: modify Advanced Filter code to specify criteria range on different worksheet

Replace the .Range("K1:K2") with Sheets("RowDeleteCriteria").Range("A2:A100") - note the absence of the dot before Sheets - like this:

Code

Sub Filter_Delete2()


    Dim lrow As Long
     
    With Sheet1
        lrow = .Cells(Rows.Count, "B").End(xlUp).Row
        .Range("B5:B" & lrow).AdvancedFilter Action:=xlFilterInPlace, _
            CriteriaRange:=Sheets("RowDeleteCriteria").Range("A2:A100"), Unique:=False
            
        .Range("B6:B" & lrow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        
        'Ignore error caused by ShowAllData if no data rows remain after the above Delete
        On Error Resume Next
        
        .ShowAllData
    End With
    
End Sub

Display More

Note that with the Sheets("RowDeleteCriteria").Range("A2:A100"), A2 contains the column heading (the same as Sheet1 B5), and A3:A100 contain the criteria values, none of which should be blank otherwise no data rows will remain after deleting the filtered rows. The On Error Resume Next statement in the code traps the error which occurs if that happens.

Jaffey · Nov 26th 2012

Re: modify Advanced Filter code to specify criteria range on different worksheet

That did the trick. Thank you so much.

modify Advanced Filter code to specify criteria range on different worksheet

Participate now!

Share