Autofilter error with array criteria of variable size

[arpd123]

Hi, it has been a while since I have submitted a question to this forum. I wrote some Excel macro code which functions as expected for a particular circumstance. It filters a database report according to certain criteria in one column. Those rows that meet that criteria can then be deleted from the report. I then had a requirement to generalize this for filtering multiple columns according to different criteria in each case, so I came up with the following subroutine to be called within my macro (FindLastColumn and FindLastRow are user-created functions for finding the last column and row with data in a sheet):

Sub Filter_Delete(strColName As String, varCriteria As Variant, strShtName As String, strWbkName As String, Optional delCol As Boolean = False)
 
    'Removes rows meeting criteria strCriteria from strColname in report sheet strShtName in workbook strWbkName and deletes strColname if delCol is true
    Dim colFound As Boolean
    Dim lngLastCol As Long, lngCol As Long
    
    lngLastCol = FindLastColumn(strShtName, strWbkName)
    lngLastRow = FindLastRow(strShtName, strWbkName)
    colFound = False
    For i = 1 To lngLastCol
        If Workbooks(strWbkName).Sheets(strShtName).Cells(6, i).Value = strColName Then
            colFound = True
            lngCol = i
        End If
    Next i
    
    Set rngData = Workbooks(strWbkName).Sheets(strShtName).Range(Workbooks(strWbkName).Sheets(strShtName).Cells(6, 1), _
    Workbooks(strWbkName).Sheets(strShtName).Cells(lngLastRow, lngLastCol))
    
    rngData.AutoFilter Field:=lngCol, _
    Criteria1:=varCriteria, _
    Operator:=xlFilterValues
    
    rngData.Offset(1).Resize(rngData.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    Workbooks(strWbkName).Sheets(strShtName).AutoFilterMode = False
    
    If delCol Then Workbooks(strWbkName).Sheets(strShtName).Columns(lngCol).EntireColumn.Delete shift:=xlToLeft
    
End Sub

It works for several different criteria arguments, however, when I call it like so

Call Filter_Delete("Qual Date", Array("<>"), wsQualSheet.Name, wbReport.Name, True)

I get an error dialogue box telling me that "Autofilter method of Range class failed."

I read somewhere else online that VBA doesn't like you using the Array function in your arguments, so if you instead assign the Array function to a variable, like

arrCrit = Array("<>")
Call Filter_Delete("Qual Date", arrCrit, wsQualSheet.Name, wbReport.Name, True)

then it may work. And I could have sworn that after I initially made this change it was working, but after some refactoring it does not anymore.

However, if I call it like this

arrCrit = Array("Criterion1", "Criterion2", "Criterion3")
Call Filter_Delete("Qual Name", arrCrit, wsQualSheet.Name, wbReport.Name, True)

(assuming that "Criterion1", "Criterion2" and "Criterion3" are actual values to be expected in the column) it doesn't complain and it does what I expect.

The wsQualSheet and wbReport objects are a worksheet and a workbook respectively (wsQualsheet is contained within wbReport, and both exist as named at runtime). Typing

?typename(Array("<>"))

in the Immediate Window indicates that it is of type Variant, and the supplied argument to the Array function is definitely a string, so I would expect compatibility with the Autofilter function. Looking at the values of all the other variables in the debugger indicates they are what is required, so what am I missing?

I can think of some more long-winded workarounds to this, but why does it not like the single argument "<>" passed in an array?

 

[GWteB]

What you're probably missing are the following facts:
1. Typing ?typename(Array("<>")) in the Immediate Window results in Variant(), so an array (obviously, since using the Array() function).
Variant() is an array, Variant (without the brackets) is not. Variant is a data type for all variables that are not explicitly declared as some other type (using statements such as Dim, Private, Public, or Static). When you run this code step by step using F8 key and watching the Local Variable Window, in particular the Type column and folding out the B and C variable when the + sign appears, you see what I mean. For instance, the C variable is not declared as an array, yet it will be one.

    Dim A()     As Variant
    Dim B(0)    As Variant
    Dim C       As Variant
    Dim X       As Integer
    B(0) = "@@"
    C = Array(1, 9.35)
    X = 1
    Y = 2
    Z = X + Y
End Sub

2. An array is a "special" data type since it may contain one or more "members" (you are aware of that of course). Sometimes Autofilter expects a string as a criteria, sometimes an array with specific members of specific data types. The only thing I can think of for this moment is that one or more criteria are not well formatted before passing them on to the Filter function.

 

[arpd123]

Thanks for the reply. I actually looked at the problem again, and have got it working. That parameter is in general multiple criteria passed in as an array but it also works as a single criterion in a string (i.e. not attempting to put it in an array with "Array(stringValue)". So, instead of this

Call Filter_Delete("Qual Date", Array("<>"), wsQualSheet.Name, wbReport.Name, True)

I do now do this

Call Filter_Delete("Qual Date", "<>", wsQualSheet.Name, wbReport.Name, True)

which works.

 

[GWteB]

You're welcome!

I do now do this .... which works.

Of course that works
It seemed you had a special reason to pass on an array to the AutoFilter method. Thank you for letting me know and posting your outcome.