Share via

Worksheet.AutoFilter Property (2007 System)

  • Article

Gets a AutoFilter that provides information about filtered lists on the worksheet if filtering is enabled. Gets nulla null reference (Nothing in Visual Basic) if filtering is off.

Namespace:  Microsoft.Office.Tools.Excel
Assembly:  Microsoft.Office.Tools.Excel.v9.0 (in Microsoft.Office.Tools.Excel.v9.0.dll)

Syntax

'Declaration
<BrowsableAttribute(False)> _
Public ReadOnly Property AutoFilter As AutoFilter

'Usage
Dim instance As Worksheet 
Dim value As AutoFilter 

value = instance.AutoFilter

[BrowsableAttribute(false)]
public AutoFilter AutoFilter { get; }

[BrowsableAttribute(false)]
public:
property AutoFilter^ AutoFilter {
    AutoFilter^ get ();
}

public function get AutoFilter () : AutoFilter

Property Value

Type: AutoFilter
A AutoFilter that provides information about filtered lists on the worksheet if filtering is enabled; otherwise, nulla null reference (Nothing in Visual Basic).

Remarks

To programmatically enable filtering, use the AutoFilter(Object, Object, XlAutoFilterOperator, Object, Object) method of the Range object or the AutoFilter method of a NamedRange object.

Examples

The following code example creates a filtered list of fruits in a Range, and then uses the AutoFilter property to display the filter criteria for the list. The example then prompts the user to turn off the filter by using the AutoFilterMode property, and finally uses the FilterMode property to display whether the worksheet has a filtered list.

This example is for a document-level customization.

Private Sub ActivateAutoFilter()
    Me.Range("A1").Value2 = "Fruits" 
    Me.Range("A2").Value2 = "Banana" 
    Me.Range("A3").Value2 = "Apple" 
    Me.Range("A4").Value2 = "Banana" 
    Me.Range("A5").Value2 = "Orange" 
    Me.Range("A6").Value2 = "Apple" 
    Me.Range("A7").Value2 = "Orange" 

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

    NamedRange1.AutoFilter(1, "Apple", Excel.XlAutoFilterOperator.xlAnd, _
        VisibleDropDown:=True)

    MsgBox("The current criteria for the AutoFilter is: " & _
        Me.AutoFilter.Filters(1).Criteria1.ToString())

    If Me.AutoFilterMode Then 
        If DialogResult.Yes = MessageBox.Show("Would you like to " & _
            "turn off the filter?", "Example", MessageBoxButtons.YesNo) Then 
            Me.AutoFilterMode = False 
        End If 
    End If 

    If Me.FilterMode Then
        MsgBox("The worksheet has a filtered list.")
    Else
        MsgBox("The worksheet does not have a filtered list")
    End If 
End Sub

private void ActivateAutoFilter()
{
    this.Range["A1", missing].Value2 = "Fruits";
    this.Range["A2", missing].Value2 = "Banana";
    this.Range["A3", missing].Value2 = "Apple";
    this.Range["A4", missing].Value2 = "Banana";
    this.Range["A5", missing].Value2 = "Orange";
    this.Range["A6", missing].Value2 = "Apple";
    this.Range["A7", missing].Value2 = "Orange";

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

    NamedRange1.AutoFilter(1, "Apple",
       Excel.XlAutoFilterOperator.xlAnd, missing, true);

    MessageBox.Show("The current criteria for the AutoFilter is: " +
        this.AutoFilter.Filters[1].Criteria1.ToString());

    if (this.AutoFilterMode)
    {
        if (DialogResult.Yes == MessageBox.Show("Would you like to " +
            "turn off the filter?", "Example", MessageBoxButtons.YesNo))
        {
            this.AutoFilterMode = false;
        }
    }

    if (this.FilterMode)
    {
        MessageBox.Show("The worksheet has a filtered list.");
    }
    else
    {
        MessageBox.Show("The worksheet does not have a filtered list");
    }
}

.NET Framework Security

See Also

Reference

Worksheet Class

Worksheet Members

Microsoft.Office.Tools.Excel Namespace

AutoFilterMode

FilterMode

AutoFilter

ShowAllData