AutoFilter on multiple criteria (array of strings) isn't returning properly-CodePudding

I've turned my csv files into macro-enabled sheets to use the macro I wrote to filter a page returning any rows that have a column match with any string in the criteria array. When running it on multiple files, it only returns for a couple of strings in the array. There's no matches for any of the array values in the 200k rows I have for that file. However, if I take the first string and manually filter that same file, I get 100 matches. Is there something wrong with the function?

(I took out the "_" line breaks because they don't line up in SO.)

Here's the function:

Sub filter_child_accounts()

ActiveSheet.Range("A1:FW1").AutoFilter Field:=8, Criteria1:=Array(  "string1", "string2",
  "string3", "string4", "string5", "string6", "string7", "string8", ..., "stringN"), _ Operator:=xlFilterValues

End Sub

There's about 100 strings in the array, so I thought maybe it was too much. There's no error returned, just a blank sheet except for the headers, but I know there's at least 100 matches for the first string in the criteria array. Any help is greatly appreciated.

CodePudding user response：

This works fine for me to filter on 100 values:

Sub filter_child_accounts()
    
    Dim i As Long, arr(1 To 100),arr2
    
    For i = 1 To 100
        arr(i) = "blah" & i
    Next i
    On Error Resume Next 'in case there's no filter
    ActiveSheet.AutoFilter.ShowAllData
    On Error Goto 0      'stop ignoring errors
    
    ActiveSheet.Range("A1:J1").AutoFilter Field:=1, Criteria1:=arr, Operator:=xlFilterValues

    'Alternative approach picking the array directly from a range on another sheet
    arr2 = Application.Transpose(Sheets("Vals").Range("A1:A100").Value)
    ActiveSheet.Range("A1:J1").AutoFilter Field:=1, Criteria1:=arr2, Operator:=xlFilterValues


End Sub

Data table has "blah1", "blah2" etc in ColA