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

ttt123

Board Regular
Joined
May 31, 2006
Messages
116
Office Version
  1. 365
Platform
  1. Windows
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)

1.png
2.png



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):
3.png


or using VBA:
VBA Code:
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”
4.png



The macro recorder shows this for the process:

VBA Code:
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.
5.png




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

VBA Code:
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.
6.png


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?
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,969
Office Version
  1. 365
Platform
  1. Windows
Try it like
VBA Code:
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
 
Solution

ttt123

Board Regular
Joined
May 31, 2006
Messages
116
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,969
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,329
Messages
5,641,546
Members
417,219
Latest member
FrancoisT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top