0
A quick search around the web shows that there is a quite common question about both Excel and Google Sheets: is it possible to create filters for a non-contiguous range of columns? Suppose you have data in columns A, B, C and D and you wish to allow filtering on non-adjacent columns A and C, how do you exclude column B? Usually the answer is either you can't or it requires complicated scripting. I'm not talking about filter functions but rather the drop-down list of column values on the first row of each column you can instate, like so:
My question is why spreadsheet programs don't typically seem to include this possibility. Is there some inherent functional or technical requirement that makes this impractical? Given that all the desired filtered columns could be moved into one contiguous range and the others moved after them, it doesn't seem impossible from a technical point of view.
Mind that I'm not asking for a solution to how to get around this, just the reason for this limitation.
G_H
Posted 2019-11-12T11:54:51.157
Reputation: 101
To clarify: you're asking why the filters apply to all columns, not just one? – Alex Robinson – 2019-11-12T11:58:05.037
@AlexRobinson The question is why filters must be created for a contiguous range of columns (for example A, B and C) and can't be created for a non-contiguous range (for example A and C but not including B). The workings of the filters are quite clear to me. Neither Excel or Google Sheets allow creating filters for non-adjacent columns, multiple ranges or however you wish to define it. – G_H – 2019-11-12T12:00:06.763
1AutoFilter is a worksheet property. So there exists only one autofilter object per worksheet. The columns in the filtered range are enumerated contunuously (Field property of Filter property member), and AutoFilter object have no property which stores the filtered columns. – Akina – 2019-11-12T12:18:17.113
@Akina That sounds like this is the result of some legacy data format which is kept for compatibility reasons. Your comment looks like it would work just fine as an answer. Maybe post it as such, which some references to or quoted parts of these data definitions? – G_H – 2019-11-12T12:22:11.517
1That sounds like this is the result of some legacy data format which is kept for compatibility reasons. No, it is common "by design and can't be cured". some references to Built-in help by mentioned objects, autofiltering under macro-recorder may help too. looks like it would work just fine as an answer. Maybe post it as such My language knowledge is not enough to create an answer with suitable quality... comment may be short whereas the answer needs in more deep explaination. – Akina – 2019-11-12T12:28:02.930
@Akina Thank you for the information! If an answer is correct it is no problem that it is short. In case you are uncertain of your English language skills (they seem fine to me) someone could always edit an answer to make corrections. – G_H – 2019-11-12T12:31:09.897