<< Click to Display Table of Contents >> Filter (object) |
Access paths:
▪Application à Workbooks à Item à Sheets à Item à AutoFilter à Filters à Item
▪Application à Workbooks à ActiveSheet à AutoFilter à Filters à Item
▪Application à ActiveWorkbook à ActiveSheet à AutoFilter à Filters à Item
▪Application à ActiveSheet à AutoFilter à Filters à Item
1 Description
A Filter object represents one individual column in the active AutoFilter. You can use it to retrieve the criteria and filter conditions for the respective column.
2 Access to the object
The individual Filter objects can be accessed solely through enumerating the elements of the corresponding Filters collection.
For each AutoFilter there is exactly one instance of the Filter collection and it is called AutoFilter.Filters:
' Display the criterion for the first column of the AutoFilter
MsgBox pm.ActiveSheet.AutoFilter.Filters.Item(1).Criteria1
Please note that all properties of the Filter object are read-only. To set up a new AutoFilter, use the AutoFilter method in the Range object.
3 Properties, objects, collections and methods
Properties:
▪Operator R/O
▪Criteria1 R/O
▪Criteria2 R/O
Objects:
▪Application → Application
▪Parent → Filters
Operator (property, R/O)
Data type: Long (PmAutoFilterOperator)
Returns the type of the filter condition. The possible values are:
pmAll = 0 ' Show all rows (= do not filter)
pmAnd = 1 ' Criteria1 and Criteria2 must be met.
pmBottom10Items = 2 ' Only the n cells with the lowest values*
pmBottom10Percent = 3 ' Only the bottom n percent values*
pmOr = 4 ' Criteria1 or Criteria2 must be met.
pmTop10Items = 5 ' Only the n highest values*
pmTop10Percent = 6 ' Only the top n percent values*
pmBlank = 7 ' Show only blank rows
pmNonblank = 8 ' Show only non-blank rows
pmCustom = 9 ' User-defined filter
* In these cases, Criteria1 contains the value for "n".
Criteria1 (property, R/O)
Data type: String
Returns the criterion of the filter – for example "red" if you have filtered for the value "red".
Exception: If one of the operators pmTop10Items, pmTop10Percent, pmBottom10Items or pmBottom10Percent is used, then Criteria1 contains a numeric value indicating how many values to display.
Criteria2 (property, R/O)
Data type: String
Returns the second criterion of the filter – provided that Operator is set to pmAnd or pmOr, as two filter criteria are only possible with them.
Application (pointer to object)
Data type: Object
Returns the Application object.
Parent (pointer to object)
Data type: Object
Returns the parent object, i.e. Filters.