Manjish Posted September 18, 2009 Share Posted September 18, 2009 (edited) Hi I wanted to make a filter function in excel. I don't know if it has been implemented before, but I made 1. Here it is: ; #FUNCTION# ==================================================================================================================== ; Name...........: _ExcelFilter ; Description ...: Applies a filter to an excel file. ; Syntax.........: _ExcelFilter($oExcel,$sRangeorRow, $iColumn, $sField, $sCriteria) ; Parameters ....: $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew() ; $sRangeOrRow - The range in A1 format, or a row number for R1C1 format ; $iColumn - The specified column number for R1C1 format (default = 1) ; $sField- the field no. where filter is to be applied. ; $sCriteria- the criteria for filter is to be applied. For e.g filter by country name "India". ; Return values .: Success - Returns 1 ; Failure - Returns 0 and sets @error on errors: ; @error=1 - Specified object does not exist ; @error=2 - Row or column invalid ; @extended=0 - Row invalid ; @extended=1 - Column invalid ; Author ........: Manjish Naik (naikma@gmail.com) ; Modified.......: None as yet ; Remarks .......: None ; Related .......: ; Link ..........; ; Example .......; _ExcelFilter($oExcel,1,2,2,"Amit Ravindra,Patil"), this filters out all entries except the one having "Manager Name" as "Amit Ravindra,Patil". ; The "manager name" is second filter field located at 1 row and 2nd column, hence $sField=2. ; =============================================================================================================================== Func _ExcelFilter($oExcel,$sRangeorRow, $iColumn, $sField, $sCriteria) If Not StringRegExp($sRangeOrRow, "[A-Z,a-z]", 0) Then If $sRangeOrRow < 1 Then Return SetError(2, 0, 0) If $iColumn < 1 Then Return SetError(2, 1, 0) $oExcel.ActiveSheet.Cells($sRangeOrRow, $iColumn).Select $oExcel.selection.Autofilter($sField, $sCriteria) Else $oExcel.ActiveSheet.Range($sRangeOrRow).Select $oExcel.selection.Autofilter($sField, $sCriteria) EndIf EndFunc An Example: #include<excel.au3> $sfilepath=@DesktopDir&"\Ist Aug Six Sigma Data.xls" $oExcel=_ExcelBookOpen($sFilepath) _ExcelFilter($oExcel,1,2,2,"Amit Ravindra,Patil") Edited September 18, 2009 by Manjish [font="Garamond"]Manjish Naik[/font]Engineer, Global Services - QPSHoneywell Automation India LimitedE-mail - Manjish.Naik@honeywell.com Link to comment Share on other sites More sharing options...
Manjish Posted September 21, 2009 Author Share Posted September 21, 2009 Can we included this in the Excel UDF? If yes, who do I need to contact? [font="Garamond"]Manjish Naik[/font]Engineer, Global Services - QPSHoneywell Automation India LimitedE-mail - Manjish.Naik@honeywell.com Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now