NamedRange.AutoFilter Method

Reference

Definition

Namespace:: Microsoft.Office.Tools.Excel

Assembly:: Microsoft.Office.Tools.Excel.dll

Important

Some information relates to prerelease product that may be substantially modified before it’s released. Microsoft makes no warranties, express or implied, with respect to the information provided here.

Filters a list using the AutoFilter.

public object AutoFilter (object Field, object Criteria1, Microsoft.Office.Interop.Excel.XlAutoFilterOperator Operator = Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd, object Criteria2, object VisibleDropDown);

abstract member AutoFilter : obj * obj * Microsoft.Office.Interop.Excel.XlAutoFilterOperator * obj * obj -> obj

Public Function AutoFilter (Optional Field As Object, Optional Criteria1 As Object, Optional Operator As XlAutoFilterOperator = Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd, Optional Criteria2 As Object, Optional VisibleDropDown As Object) As Object

Parameters

Field: Object

The offset of the field on which you want to base the filter (from the left of the list; the leftmost field is field 1 (one)).

Criteria1: Object

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: XlAutoFilterOperator

Can be one of the following XlAutoFilterOperator constants: xlAnd xlBottom10Items xlBottom10Percent xlOr xlTop10Items xlTop10Percent Use xlAnd and xlOr with Criteria1 and Criteria2 to construct compound criteria.

Criteria2: Object

The second criteria (a string). Used with Criteria1 and Operator to construct compound criteria.

VisibleDropDown: Object

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.

Returns

Object

Examples

The following code example sets cells A1 through A5 to five different name strings and then uses the AutoFilter method to filter for the name "Robert".

This example is for a document-level customization.

private void SetAutoFilter()
{
    this.Range["A1"].Value2 = "Kathleen";
    this.Range["A2"].Value2 = "Robert";
    this.Range["A3"].Value2 = "Paul";
    this.Range["A4"].Value2 = "Harry";
    this.Range["A5"].Value2 = "George";

    Microsoft.Office.Tools.Excel.NamedRange namedRange1 =
        this.Controls.AddNamedRange(this.Range["A1", "A5"],
        "namedRange1");

    namedRange1.AutoFilter(1, "Robert",
        Excel.XlAutoFilterOperator.xlAnd, true);
}

Private Sub SetAutoFilter()
    Me.Range("A1").Value2 = "Kathleen"
    Me.Range("A2").Value2 = "Robert"
    Me.Range("A3").Value2 = "Paul"
    Me.Range("A4").Value2 = "Harry"
    Me.Range("A5").Value2 = "George"

    Dim namedRange1 As Microsoft.Office.Tools.Excel.NamedRange _
        = Me.Controls.AddNamedRange(Me.Range("A1", "A5"), _
        "namedRange1")

    namedRange1.AutoFilter(1, "Robert", _
        Excel.XlAutoFilterOperator.xlAnd, , True)
End Sub

Remarks

If you omit all the arguments, this method simply toggles the display of the AutoFilter drop-down arrows in the NamedRange control.

Optional Parameters

For information on optional parameters, see Optional Parameters in Office Solutions.

Applies to

Share via