(COM) AHK and Excel - How to filter a column using multiple criteria? Topic is solved

[Renets]

Hi!

I need to filter a column with multiple criteria, Joe did a good video here https://www.youtube.com/watch?v=PS3Ud3GOnR4, however, it only allows one criteria

In Excel Macros the code is like this

Code: Select all

Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter _
 Field:=1, _
 Criteria1:=Array("1", "3", "Seattle", "Redmond"), _
 Operator:=xlFilterValue

Note the "Array(...)", which I think is the problem on AHK

I've tried all sort of things, like so:

Code: Select all

XL.Application.ActiveSheet.Range(XL_Used_RG(XL,Header:=0)).AutoFilter(XL_String_To_Number("A"), "Criteria1:=Array(""136"", ""210"")")
; --
temp_arrray := [120, 122]
XL.Application.ActiveSheet.Range(XL_Used_RG(XL,Header:=0)).AutoFilter(XL_String_To_Number("A"), temp_arrray )
;--
XL.Application.ActiveSheet.Range(XL_Used_RG(XL,Header:=0)).AutoFilter(XL_String_To_Number("A"), "120,122" )

With no success, I event tried with ComObjArray, but the problem with this one is that it only filters the last (MaxIndex) value of the array (only 1)

Code: Select all

Arr1 := ComObjArray(0xC, 4) ; 
Arr1[0] := 120
Arr1[1] := 123
Arr1[2] := 122
Arr1[3] := 128

XL.Application.ActiveSheet.Range(XL_Used_RG(XL,Header:=0)).AutoFilter(XL_String_To_Number("A"), Arr1)

Any Ideas or suggestions?

* I am using Joe Glines Excel Library for the "XL_String_To_Number" and "XL_Used_RG"

The Excel data is simple:

Available
120
121
122
123
126
127
128
129

[awel20]

Code: Select all

xlFilterValues := 7
Criteria1 := ComObjArray(0xC, 4)
Criteria1[0] := "120"
Criteria1[1] := "122"
Criteria1[2] := "123"
Criteria1[3] := "128"
xlApp := ComObjActive("Excel.Application")
xlApp.Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter(1, Criteria1, xlFilterValues)
xlApp := ""

Code: Select all

xlFilterValues := 7
Criteria1 := ComObjArray(0xC, 4)
Criteria1[0] := "120"
Criteria1[1] := "122"
Criteria1[2] := "123"
Criteria1[3] := "128"
xlApp := ComObjActive("Excel.Application")
xlApp.Worksheets("Sheet1").Range("A:A").AutoFilter(1, Criteria1, xlFilterValues)
xlApp := ""

[Renets]

Ohh wooow so that was it!!
The value of the parameter "xlFilterValues", digging into it I found this references for it its values
https://docs.microsoft.com/en-us/office/vba/api/excel.xlautofilteroperator

And this is the documentation for AutoFilter
https://docs.microsoft.com/en-us/office/vba/api/excel.range.autofilter

I had only tested it with "2", which according to the docs, 2 is:

Code: Select all

xlOr	2	Logical OR of Criteria1 or Criteria2

And also without any value, which was causing the problems, filtering only one value

Although I just tested it but using the

Code: Select all

xlApp := ComObjActive("Excel.Application")

(Error: 0x800401E3 - Operation unavailable) ; But I do have opened the Excel with the data

Didn't work for me, maybe those of random bugs, maybe just need to reset my computer.
BUT

Using the variable with the function from Joe's library, does the trick! This works for me:

When the data is on a table:

Code: Select all

XL :=XL_Handle(1)
xlFilterValues := 7
Criteria1 := ComObjArray(0xC, 4)
Criteria1[0] := "120"
Criteria1[1] := "122"
Criteria1[2] := "123"
Criteria1[3] := "128"
XL.Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter(1, Criteria1, xlFilterValues)
XL := ""

When the data is not on table:

Code: Select all

XL :=XL_Handle(1)
xlFilterValues := 7
Criteria1 := ComObjArray(0xC, 4)
Criteria1[0] := "120"
Criteria1[1] := "122"
Criteria1[2] := "123"
Criteria1[3] := "128"
XL.Worksheets("Sheet1").Range("A:A").AutoFilter(1, Criteria1, xlFilterValues)
; XL.Application.ActiveSheet.Range("A:A").AutoFilter(1, Criteria1, xlFilterValues) ; Using the ActiveSheet. 1 is Column A in "AutoFilter(1,..."

XL := ""

Many thanks awel20!!