ListObject.AutoFilter Property

Gets a filtered table.

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

Syntax

'Declaration
ReadOnly Property AutoFilter As AutoFilter
AutoFilter AutoFilter { get; }

Property Value

Type: Microsoft.Office.Interop.Excel.AutoFilter
An AutoFilter that contains a filtered table.

Examples

The following code example adds a ListObject to the worksheet, which corresponds to a table in Excel. The example then populates the table with two rows of arbitrary data and specifies a filter on the table that filters out all the rows whose first column value is not equal to the string value "bb". Next, the example displays the number of active filters found in the table.

This example is for a document-level customization.

Private Sub FilterListObject()
    ' Create ListObject control (table) and set table style 
    Dim employeeTable As Microsoft.Office.Tools.Excel.ListObject = _
        Me.Controls.AddListObject(Me.Range("A1"), "employeeTable")

    ' Populate table with some data 
    Dim rng As Excel.Range
    rng = employeeTable.InsertRowRange
    rng(ColumnIndex:=1).Value2 = "bb"
    rng(ColumnIndex:=2).Value2 = "b1" 
    Dim row2 As Excel.ListRow = employeeTable.ListRows.AddEx( _
        AlwaysInsert:=True)
    rng = row2.Range
    rng(ColumnIndex:=1).Value2 = "aa"
    rng(ColumnIndex:=2).Value2 = "a1" 

    ' Set a filter        
    employeeTable.Range.AutoFilter(1, "bb")        
    Dim activeFilterCount As Integer = 0
    For Each filter As Excel.Filter In employeeTable.AutoFilter.Filters
        If filter.On = True Then
            activeFilterCount += 1
        End If 
    Next
    MessageBox.Show("There are " + activeFilterCount.ToString() _
        + " active filter(s) for table " + employeeTable.Name + ".")

End Sub
private void FilterListObject()
{
    // Create ListObject control (table) and set table style
    Microsoft.Office.Tools.Excel.ListObject employeeTable =
        this.Controls.AddListObject(this.Range["A1"], 
        "employeeTable");

    // Populate table with some data
    Excel.Range rng;
    rng = employeeTable.InsertRowRange;
    ((Excel.Range)(rng[1])).Value2 = "bb";            
    ((Excel.Range)(rng[2])).Value2 = "b1";
    Excel.ListRow row2 = employeeTable.ListRows.AddEx(true);
    rng = row2.Range;
    ((Excel.Range)(rng[1])).Value2 = "aa";
    ((Excel.Range)(rng[2])).Value2 = "a1";

    // Set a filter        
    employeeTable.Range.AutoFilter(1, "bb", 
        Excel.XlAutoFilterOperator.xlFilterValues);        
    int activeFilterCount = 0;
    foreach (Excel.Filter filter in employeeTable.AutoFilter.Filters)
    {
        if (filter.On == true)
        {
            activeFilterCount += 1;
        }
    }
    MessageBox.Show("There are " + activeFilterCount.ToString()
        + " active filter(s) for table " + employeeTable.Name + ".");

}

.NET Framework Security

See Also

Reference

ListObject Interface

Microsoft.Office.Tools.Excel Namespace