Sets/unsets filter definitions and filters the range
#include <Excel.au3>
_Excel_FilterSet ( $oWorkbook, $vWorksheet, $vRange, $iField [, $sCriteria1 = Default [, $iOperator = Default [, $sCriteria2 = Default]]] )
| $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 |
| 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 |
Dynamic filter:
To filter data columns you set $iOperator to $xlFilterDynamic and $sCriteria1 to any of the XlDynamicFilterCriteria enumeration.
See example 2 for details.
#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.")
#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.")