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
Get
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",missing],
"employeeTable");
// Populate table with some data
Excel.Range rng;
rng = employeeTable.InsertRowRange;
((Excel.Range)(rng[missing, 1])).Value2 = "bb";
((Excel.Range)(rng[missing,2])).Value2 = "b1";
Excel.ListRow row2 = employeeTable.ListRows.AddEx(missing,true);
rng = row2.Range;
((Excel.Range)(rng[missing,1])).Value2 = "aa";
((Excel.Range)(rng[missing,2])).Value2 = "a1";
// Set a filter
employeeTable.Range.AutoFilter(1, "bb",
Excel.XlAutoFilterOperator.xlFilterValues,missing,missing);
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
- Full trust for the immediate caller. This member cannot be used by partially trusted code. For more information, see Using Libraries from Partially Trusted Code.