Autofilter error with array criteria of variable size

arpd123

New Member
Joined
May 14, 2018
Messages
24
Office Version
  1. 365
Platform
  1. Windows
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):

VBA Code:
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

VBA Code:
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

VBA Code:
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

VBA Code:
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
VBA Code:
?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?
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
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.
VBA Code:
    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

New Member
Joined
May 14, 2018
Messages
24
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
Call Filter_Delete("Qual Date", Array("<>"), wsQualSheet.Name, wbReport.Name, True)
I do now do this
VBA Code:
Call Filter_Delete("Qual Date", "<>", wsQualSheet.Name, wbReport.Name, True)
which works.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,908
Messages
5,638,932
Members
417,059
Latest member
clinical

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top