ListObject.AutoFilter Property
Definition
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.
Gets a filtered table.
public:
property Microsoft::Office::Interop::Excel::AutoFilter ^ AutoFilter { Microsoft::Office::Interop::Excel::AutoFilter ^ get(); };
public Microsoft.Office.Interop.Excel.AutoFilter AutoFilter { get; }
member this.AutoFilter : Microsoft.Office.Interop.Excel.AutoFilter
Public ReadOnly Property AutoFilter As AutoFilter
Property Value
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 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 + ".");
}
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