Xl autofitering on date

[jmvmla]

Hi all,
I would like to filter an XL spreadsheet based on month and year of a date
To do that , I plan to use the autofiltering action but I have a problem with the constant value "xlFilterValues" ; which seems not existing
any advises will be welcomed

thanks

jm

[td]

Assuming you are using Excel, you could do one of the following:

• Use the ObjectConstantsGet function to obtain all the enumeration values implemented by Excel
• Use the WinBatch "WIL Type Viewer" utility to load all interfaces and enumerations supported by the Excel object.  Each enumeration will have the numeric value associated with the enumeration element.
• You could perform a Web search on the value and discover the numeric value associated with the enumeration element.  If you did, you would this page as one of the search results: https://docs.microsoft.com/en-us/office/vba/api/excel.xlautofilteroperator

Once you have the numeric value you can use the number in place of the enumeration element or create a variable with the numeric value assigned to it and use the variable.  The latter makes for a more readable/ maintainable script.

[stanl]

Interesting.   There are Excel objects for filtering with code, for example Criteria1=  xlFilterAllDatesInPeriodAugust to get dates for August.  The secret would be to make it dynamic for any date.  This article may help


https://stackoverflow.com/questions/18646952/excel-autofilter-dates-in-month-period


For Tony: Const xlFilterAllDatesInPeriodAugust = 28 (&H1C)  -from Excel 2019 Object Browser

[stanl]

For Tony: Const xlFilterAllDatesInPeriodAugust = 28 (&H1C)  -from Excel 2019 Object Browser

Only reason I included this: seems filter objects are iterative, i.e. January = 21 February=22 etc.  So wondering if the new WB Map functions could build a dynamic lookup to create criteria for the Excel filter.

[td]

Only tangentially topical - but adding an optional parameter to the ObjectConstToArray function to create a map of available constants instead of a regular WIL array of constants could be useful.  Don't know if the function is used enough to make it worth the bother, however.