<< 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 superordinate object, i.e. Filters.