cnilsson76 0 Posted April 18 Share Posted April 18 (edited) I am trying to use _Excel_FilterSet to "clear" a filter from a column in a spreadsheet. By "clear" I mean that I want to show all values in the column...in other words "turn the filter off". However, I do not want to remove the filter. _Excel_FilterSet($oWorkbook, Default, Default, 0) This option REMOVES all filters from all columns in the spreadsheet. _Excel_FilterSet($oWorkbook, Default, Default, 4) Oddly, this code also removes all column filters (not just column D) - Is this the expected behavior? _Excel_FilterSet($oWorkbook, Default, Default, 4, "<>") This option will keep the filters and select all non-blanks in column D as expected. _Excel_FilterSet($oWorkbook, Default, Default, 4, "") This option will keep the filters and select all blanks in column D as expected . I'm looking for a combination of the last two. I want to keep the filter on the column but show all values including blanks. The equivalent of the "(Select All)" option if you were interacting with the filter directly in Excel. If I were in VBA, I would simply use Worksheet.Range["A1"].AutoFilter(1); to clear all the filters on the sheet (same as clicking Select All) Is this possible? Thanks! Edited April 18 by cnilsson76 Link to post Share on other sites
Nine 1,513 Posted April 18 Share Posted April 18 (edited) You know that the Excel UDF is a wrapper of the COM excel object. So once you have the objects, you can use any properties or methods available : $oWorkBook.ActiveSheet.Range("A1").AutoFilter(1) should work (untested). Edited April 18 by Nine Not much of a signature but working on it... Spoiler Block all input without UAC Save/Retrieve Images to/from Text Monitor Management (VCP commands) Tool to search content in au3 files Date Range Picker Virtual Desktop Manager Sudoku Game 2020 Overlapped Named Pipe IPC HotString 2.0 - Hot keys with string x64 Bitwise Operations Multi-keyboards HotKeySet Recursive Array Display Fast and simple WCD IPC Multiple Folders Selector GIF Animation (cached) Screen Scraping Link to post Share on other sites
water 2,642 Posted April 18 Share Posted April 18 (edited) If you remove the following line from function _Excel_FilterSet you get all data and keep the filters. Filters will only be removed when parameter $iField is set to 0. I renamed the function to _Excel_FilterSetEX so I did not have to modify the original Excel UDF. Func _Excel_FilterSetEX($oWorkbook, $vWorksheet, $vRange, $iField, $sCriteria1 = Default, $iOperator = Default, $sCriteria2 = Default) ; Error handler, automatic cleanup at end of function Local $oError = ObjEvent("AutoIt.Error", "__Excel_COMErrFunc") #forceref $oError If Not IsObj($oWorkbook) Or ObjName($oWorkbook, 1) <> "_Workbook" Then Return SetError(1, 0, 0) If Not IsObj($vWorksheet) Then If $vWorksheet = Default Then $vWorksheet = $oWorkbook.ActiveSheet Else $vWorksheet = $oWorkbook.WorkSheets.Item($vWorksheet) EndIf If @error Or Not IsObj($vWorksheet) Then Return SetError(2, @error, 0) ElseIf ObjName($vWorksheet, 1) <> "_Worksheet" Then Return SetError(2, @error, 0) EndIf If $vRange = Default Then $vRange = $vWorksheet.Usedrange ElseIf Not IsObj($vRange) Then $vRange = $vWorksheet.Range($vRange) If @error Or Not IsObj($vRange) Then Return SetError(3, @error, 0) EndIf If $iField <> 0 Then ; Set a new filter $vRange.AutoFilter($iField, $sCriteria1, $iOperator, $sCriteria2) If @error Then Return SetError(4, @error, 0) ; If no filters remain then AutoFiltermode is set off ; If $vWorksheet.Filtermode = False Then $vWorksheet.AutoFilterMode = False ; <== modify this line Else ; remove all filters $vWorksheet.AutoFilterMode = False EndIf Return 1 EndFunc ;==>_Excel_FilterSet Edited April 18 by water My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to post Share on other sites
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