Filter Geography (rich data type) columns using multiple SubField parameters via VBA

[ttt123]

I am trying to figure out how the new rich data type fields (Geography and Stocks) work with respect to auto filters and VBA.



For example, suppose I have a table with header "City" and five cities: "New York", "Los Angeles", "San Francisco", "Toronto" and "Boston". The cities have the Geography data type applied by first selecting them and selecting "Geography" on the 'Data' Ribbon (requires Microsoft 365 version of Excel)

With rich data types, you can filter by hidden fields, that are not shown in the table columns, by using AutoFilter’s SubField parameter in VBA. So for example, if I have those cities in my table, I can filter by country even if the Country column is not shown (and only the City column is shown):

or using VBA:

ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= "United States", SubField:="Country/region"

This all works well in VBA for a single sub field but these rich data types also allow you to also filter by multiple sub fields in a single. So, for example, I can filter by "Country/region" and "Time zone(s)" at the same time using the Excel user interface by applying a filter to "Country/region" first and then by doing it again, it will stack the sub field condition for "Time zone(s)" instead of overwriting the initial condition on "Country/region". If you manually do this through the user interface, the tooltip for the column will say: City: Country/region Equals “United States” and Time zone(s) Equals “Eastern Time zone”

The macro recorder shows this for the process:

ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= "United States", SubField:="Country/region"
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= "Eastern Time Zone", SubField:="Time zone(s)"

Note that the macro recorder shows this as 2 separate function calls. Unlike standard Excel data types, rich data types allow you to stack filters on multiple filters in a single column’s criteria. However, if I run the recorded macro, only the second call which filters on time zone will be applied since the second call overwrites the filter conditions that were applied on the first call.



So the VBA isn't quite so useful but I thought of retrieving the current auto filter conditions via VBA to see if I could figure out how to make a single call with multiple SubField parameters via VBA but there seems to be no way to retrieve the SubField parameter via VBA.

I attempted to create a single line VBA call to acheive filtering on multiple sub fields:

ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:="United States", Criteria2:="Eastern Time Zone", SubField:=Array("Country/region", "Time zone(s)"), Operator:=xlAnd

This code compiles and runs but when you hover over the column header, the tooltip shows "City: Time zone(s) Equals "United States" and Equals "Eastern Time Zone". So it is not functioning as I expected. I would say that this appears to be a bug in the Excel/VBA API but the documentation on the SubField parameter is almost non-existent and it does not mention if it is possible to pass multiple sub field parameters even though I appear to have been able to do that using an Array.

Another interesting observation is that you can sort on a hidden sub field using the filter's Sort Ascending/Descending buttons but the "Custom Sort" dialog does not seem to support sorting on sub fields of rich data types.



So in conclusion I have these questions:

1. Is there any way to filter on multiple (hidden) sub fields via VBA? If so, how do you do it either in a single call or multiple calls (where subsequent calls dont remove the filter criteria from the first call)?
2. Is there any way to retrieve the SubField parameter, via VBA, that was previously used to apply a filter on a column with rich data? I only seem to be able to get Field, Criteria1, Criteria2, and Operator but perhaps there is some other way like somehow viewing the XML for the worksheet? I tried Range.Value(11) but it was not in the XML there
3. Is there any way to retrieve a list of all possible SubField parameters for a column containing rich data via VBA?

 

[Fluff]

Try it like

ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:="United States", SubField:="Country/region"
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:=Array("Eastern Time Zone"), SubField:="Time zone(s)", Operator:=xlFilterValues

 

[ttt123]

Thank you very much @Fluff! That was very helpful. How did you go about finding that syntax for stacking different filter criteria for different sub fields? Is it documented somewhere or did you just find that that worked by experimentation?

Do you know if there is any way to retreive via VBA SubField parameters that were previously applied to a filter or is there no way to do so with the current API?

Is there any way to get a list of all possible SubField parameters for a particular rich data type? The FIELDVALUE function takes a field_name parameter and I see some sites that list out all the possible values for the Geography and Stocks types but there are other rich data types or new fields may be added to existing data types so it would be nice if there was a function that could return a dynamic array of possible values, but as far as I can tell that function doesn't seem to exist.

 

[Fluff]

Is it documented somewhere or did you just find that that worked by experimentation?

Pure trial & error.

I have never used the Data Types, so cannot answer your further questions.