'Declaration
Function AutoFilter ( _
Field
As Object, _
Criteria1
As Object, _
Operator
As XlAutoFilterOperator, _
Criteria2
As Object, _
VisibleDropDown
As Object _
)
As Object
'Usage
Dim instance
As Range
Dim Field
As Object
Dim Criteria1
As Object
Dim Operator
As XlAutoFilterOperator
Dim Criteria2
As Object
Dim VisibleDropDown
As Object
Dim returnValue
As Object
returnValue = instance.AutoFilter(Field, _
Criteria1, Operator, Criteria2, VisibleDropDown)
Field - The integer offset of the field on which you want to base the filter (from the left of the list; the leftmost field is field one).
The
criteria (a string; for example, "101"). Use "=" to find blank fields, or use "<>" to find nonblank fields. If this argument is omitted, the criteria is
All. If
Operator is
xlTop10Items,
Criteria1 specifies the number of items (for example, "10").
Operator -
Can be one of the following
XlAutoFilterOperator constants:
- xlAnddefault
- xlBottom10Items
- xlBottom10Percent
- xlOr
- xlTop10Items
- xlTop10Percent
Use
xlAnd and
xlOr with
Criteria1 and
Criteria2 to construct compound criteria.
The
second criteria (a string). Used with Criteria1 and Operator to construct compound criteria.
VisibleDropDown: True to display the AutoFilter drop-down arrow for the filtered field;
False to hide the AutoFilter drop-down arrow for the filtered field.
True by default.
Ex:
Workbooks("Combining excels for compliance.xlsm").Sheets("Test Lab data").Range("A:H").AutoFilter Field:=1, Criteria1:=MasterPrjid & "*", Operator:=xlFilterValues, VisibleDropDown:="True"
' Field 1 = Maser project name(here i would like to apply filter)
' Criterial = Master project id with some characters appended to it
' Not sure about other filter atrributes, even if you remove them its working..
Issue: I tried to use "*" - regular expression as mentioned in teh below code:
Ben= "Heermance"
ActiveSheet.Range("$A$1:$CN$25425").AutoFilter Field:=28, Criteria1:=Array( _
"Goel Vikas", "*" & Ben & "*", "Radmand Payman", "Ray Michael H", "Tiku Sripriya" _
, "Wilson Sheila K"), Operator:=xlFilterValues
But looks like regular expression does not work just like that. Mentiond the scenario in handson.
