AutoFilter Method of Range Class Failed - Only With Array()

[ScottDoom]

Hello,

I have tried Googling & searching this forum, but only seem to be finding unrelated issues that do not help me solve my problem. I have a macro set up with a final portion that filters through some columns and deletes and/or moves rows of information depending on filtered results.

All of the code works, except for one portion which gives the Runtime 1004 Method of Range Class Failed error. The code is exactly the same, and the only difference is the use of an Array (containing three search values) instead of a single Criteria.

Dim lFinalLastRow As Long [COLOR="#00FF00"]' Repeatedly used to define the last row of data.[/COLOR]

Sheets("FileList").Select [COLOR="#00FF00"]' Select the correct sheet in the WB.[/COLOR]
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False [COLOR="#00FF00"]' Turn off filtering in case it is already on.[/COLOR]

lFinalLastRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("A1:S" & lFinalLastRow).Select [COLOR="#00FF00"]' Selects all the data (begins in Col A and ends in Col S, Row length changes).[/COLOR]
    Selection.AutoFilter [COLOR="#00FF00"]' Turns filtering on for all columns.[/COLOR]

lFinalLastRow = Range("A" & Rows.Count).End(xlUp).Row
    [COLOR="#FF0000"]ActiveSheet.Range("$A$1:$S$" & lFinalLastRow).AutoFilter Field:=2, Criteria1:=Array("111111111", "999999999", "xxxxxxxxx"), Operator:=xlFilterValue[/COLOR]
    lFinalLastRow = Range("A" & Rows.Count).End(xlUp).Row
    Rows("2:" & lFinalLastRow).Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("$A$1:$S$" & lFinalLastRow).AutoFilter Field:=2
    
lFinalLastRow = Range("A" & Rows.Count).End(xlUp).Row
    ActiveSheet.Range("$A$1:$S$" & lFinalLastRow).AutoFilter Field:=1, Criteria1:="05*"
lFinalLastRow = Range("A" & Rows.Count).End(xlUp).Row
    Rows("2:" & lFinalLastRow).Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("$A$1:$S$" & lFinalLastRow).AutoFilter Field:=1
    
lFinalLastRow = Range("A" & Rows.Count).End(xlUp).Row
    ActiveSheet.Range("$A$1:$S$" & lFinalLastRow).AutoFilter Field:=13, Criteria1:="TRUE"
    Rows("1:" & lFinalLastRow).Select
    Selection.Copy
    Sheets("DNF").Select
    ActiveSheet.Paste
    Range("A1").Select
    Sheets("FileList").Select
    Rows("2:" & lFinalLastRow).Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("$A$1:$S$" & lFinalLastRow).AutoFilter Field:=13

[COLOR="#00FF00"]'...and a few more filters following the above pattern, which all work...[/COLOR]

 

[MARK858]

Works fine for me as long as there is at least 1 of the criteria in column B and there is an s at the end of xlFilterValues

 

[ScottDoom]

Oh no, that missing "s" did the trick. I am not sure how that went missing.



Thanks much!

 

[MARK858]

Happy it helped and welcome to the forum.