# Function Reference

# _Excel_FilterSet

Sets/unsets filter definitions and filters the range

#include <Excel.au3>
_Excel_FilterSet ( $oWorkbook, $vWorksheet, $vRange, $iField [, $sCriteria1 = Default [, $iOperator = Default [, $sCriteria2 = Default]]] )

## Parameters

$oWorkbook | Excel workbook object
$vWorksheet | Name, index or worksheet object to be filtered. If set to keyword Default the active sheet will be filtered
$vRange | A range object or an A1 range to specify the columns to filter on. Use keyword Default to filter on all used columns of the specified worksheet
$iField | Integer offset of the field on which you want to base the filter (the leftmost field is field one). If set to 0 all Autofilters on the worksheet will be removed
$sCriteria1 | [optional] The criteria (a string or an array of strings). 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")
$iOperator | [optional] One of the constants of the XlAutoFilterOperator enumeration specifying the type of filter
$sCriteria2 | [optional] The second criteria (a string). Used with Criteria1 and Operator to construct compound criteria

## Return Value

Success: | 1.
Failure: | 0 and sets @error.
@error: | 1 - $oWorkbook is not an object or not a workbook object 2 - $vWorksheet name or index are invalid or $vWorksheet is not a worksheet object. @extended is set to the COM error code 3 - $vRange is invalid. @extended is set to the COM error code 4 - Error returned by the Filter method. @extended is set to the COM error code

## Remarks

Dynamic filter:
To filter data columns you set $iOperator to $xlFilterDynamic and $sCriteria1 to any of the XlDynamicFilterCriteria enumeration.
See example 2 for details.

## Related

_Excel_FilterGet

## Example

### Example 1

    #include "Extras\HelpFileInternals.au3"

    #include <Excel.au3>
    #include <MsgBoxConstants.au3>

    ; Create application object and open an example workbook
    Local $oExcel = _Excel_Open()
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_FilterSet Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    Local $oWorkbook = _Excel_BookOpen($oExcel, _Extras_PathFull("_Excel1.xls"), True)
    If @error Then
        MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_FilterSet Example", "Error opening workbook '" & _Extras_PathFull("_Excel1.xls'.") & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        _Excel_Close($oExcel)
        Exit
    EndIf
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_FilterSet Example", "Press Enter to set the first filter!")

    ; Filter the complete active worksheet on column 1.
    ; Only show rows with content >20 and <40 in the specified column.
    _Excel_FilterSet($oWorkbook, Default, "A1:E30", 1, ">20", 1, "<40")
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_FilterSet Example 1", "Error filtering data." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_FilterSet Example 1", "Filtered on column 'A'. Only show rows with values >20 and <40.")

    ; Add a filter to column 2.
    ; Only show rows with content <310 in the specified column.
    _Excel_FilterSet($oWorkbook, Default, Default, 2, "<310")
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_FilterSet Example 2", "Error filtering data." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_FilterSet Example 1", "Added filter on column 'B'. Only show rows with values <310.")

    ; Remove the filter from column 1.
    _Excel_FilterSet($oWorkbook, Default, Default, 1)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_FilterSet Example 3", "Error filtering data." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_FilterSet Example 1", "Removed filter from column 'A'.")

    ; Only display selected values (20, 40 and 60) of column 2.
    Local $aShow[] = ["20", "40", "60"]
    _Excel_FilterSet($oWorkbook, Default, Default, 2, $aShow, $xlFilterValues)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_FilterSet Example 4", "Error filtering data." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_FilterSet Example 1", "Set filter in column 'B' to only display selected values (20, 40 and 60).")

    ; Remove all filters.
    _Excel_FilterSet($oWorkbook, Default, Default, 0)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_FilterSet Example 5", "Error filtering data." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_FilterSet Example 1", "All filters removed.")

### Example 2

    #include "Extras\HelpFileInternals.au3"

    #include <Excel.au3>
    #include <MsgBoxConstants.au3>

    ; Create application object and open an example workbook
    Local $oExcel = _Excel_Open()
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_FilterSet Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    Local $oWorkbook = _Excel_BookOpen($oExcel, _Extras_PathFull("_Excel1.xls"), True)
    If @error Then
        MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_FilterSet Example", "Error opening workbook '" & _Extras_PathFull("_Excel1.xls'.") & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        _Excel_Close($oExcel)
        Exit
    EndIf
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_FilterSet Example", "Press Enter to set the first filter!")

    ; Filter Column M by date. Show all dates in october of 2013 and 2014.
    ; 0-year, 1-month, 2-day, 3-hour, 4-minute, 5-second
    Local $aShow[] = [1, "01/10/2013", 1, "01/10/2014"]
    _Excel_FilterSet($oWorkbook, Default, Default, 13, Default, $xlFilterValues, $aShow)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_FilterSet Example 6", "Error filtering data." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_FilterSet Example 2", "Column 'M' filtered by date. Show all dates in October of 2013 and 2014.")

    ; Filter Column M by date. Show all dates of last year.
    _Excel_FilterSet($oWorkbook, Default, Default, 13, $xlFilterLastYear, $xlFilterDynamic)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_FilterSet Example 7", "Error filtering data." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_FilterSet Example 2", "Column 'M' filtered by date. Show all dates of last year.")