Jump to content

How to use _Excel_FilterSet to clear a filter but not remove it?


Recommended Posts

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 by cnilsson76
Link to post
Share on other sites

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 by Nine
Link to post
Share on other sites

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 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 - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...