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 5 for details.

Related

_Excel_FilterGet

Example

Example 1

#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, @ScriptDir & "\Extras\_Excel1.xls", True)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_FilterSet Example", "Error opening workbook '" & @ScriptDir & "\Extras\_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 <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, @ScriptDir & "\Extras\_Excel1.xls", True)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_FilterSet Example", "Error opening workbook '" & @ScriptDir & "\Extras\_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.")