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

[cnilsson76]

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, 2022 by cnilsson76

[Nine]

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, 2022 by Nine

[water]

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, 2022 by water