How to automatically adjust autofilter (no VBA) applied to a dynamic array?

[vincethesun]

Hi folks,

I'm not sure if this is a simple tricky function hidden somewhere but I've been searching for an answer to this question or something similar but didn't find anything relevant about it.

The problem is that I created a table using dynamic array formulas with Excel 365 and applied autofilter to this table-like dynamic array. For instance, a quite simple array with SEQUENCE function: "=SEQUENCE(2, 10)" which gives a 2*10 dynamic array. If I apply an autofilter to this array, then the little filter buttons expand from column 1 to column 10.

Now if I change the formula to "=SEQUENCE(2, 8)", the array becmes 2*8 so I'm expecting the little filter buttons to automatically expand from 10 columns initially to 8 columns now. However, this is not happening. The autofilter buttons still expand from column 1 to column 10 even though columns 9 and 10 become blank.

Conversely, if the initial array is 2*8 with autofilter applied, and when it changes from 2*8 to 2*10, the autofilter won't add 2 more autofilter buttons to columns 9 and 10.

So, I'm wondering if there is a permanent way (not using VBA) to make autofilter dynamically adjust to an dynamic array span.

Appreciate if someone can shed light on this and feel free to post your comment if you are interested in the same.

Thanks.

 

[severynm]

So, I'm wondering if there is a permanent way (not using VBA) to make autofilter dynamically adjust to an dynamic array span.

Not that I know of. I think you are stuck with toggling the filter off/on to reset it each time you need it changed.

 

[vincethesun]

Not that I know of. I think you are stuck with toggling the filter off/on to reset it each time you need it changed.

Hi severynm,

Absolutely. If there's no such magic of dynamic autofilter option for dynamic arrays, what a pity! Highly recommend them adding such option to cope with the all-powerful dynamic arrays. Dynamic functionalities should work together, right?

 

[severynm]

Highly recommend them adding such option to cope with the all-powerful dynamic arrays. Dynamic functionalities should work together, right?

I mean, that's literally what VBA and/or the newer Office Scripts are for.