SOLVED

Filter Non-Adjacent Columns

New Contributor

Is it possible to use the filer function for columns that aren't right next to each other? I don't want all the arrows across the whole table, only in 2 collums. I would prefer not using VBA but any help is appreciated.

7 Replies

Hi @Dean_T-B,

as you can see no one has answered so far. I would think that's because no one really understands your question. Neither do I. Maybe you can make an example and explain exactly what you want. There is no filet function at all. Is this a translation problem? What does this have to do with arrows? You would have to describe in much more detail what you want. Does not work like that.

Hi @dscheikey ,

Apologies for the typo, I was asking about the "Filter" function found in the "Data" tab.

I am attempting to apply the drop-down filter menus to two non-adjacent columns in the same table. 
Please see the below example of what i am trying to achieve.

Dean_TB_0-1659300823361.png

 

best response confirmed by Dean_T-B (New Contributor)
Solution

@Dean_T-B 

Let's say the table is named Table1.

Press Alt+F11 to activate the Visual Basic Editor.

Press Ctrl+G to activate the Immediate window in the Visual Basic Editor.

Type or copy/paste the following line:

 

ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=2, VisibleDropdown:=False

 

With the insertion point anywhere in that line, press Enter.

Thanks @Hans Vogelaar, that worked perfectly. Is there a way to do this if my data is not in a table? I have a much bigger spreadsheet I'd like to do this for as well.

@Dean_T-B 

Yes: instead of ActiveSheet.ListObjects("Table1").Range specify the range it applies to, for example Range("A1:T100")

That's great @Hans Vogelaar, you've been a massive help!!

 

One final thing, is it possible to filter by headings?

Please see below as an example, I'm only wanting the letters "A" and "B" to show up on the drop-down menu, but I'd like all of the information in its category to still show. I'm hoping there's a way to do this without the visual matrix.

Dean_TB_1-1659307051495.png

 

 

@Dean_T-B 

No, that is not possible, as far as I know.

 

As an alternative, you might group the rows under A and group the rows under B

You'll find Group in the Outline group of the Data tab of the ribbon.

You can then expand and collapse rows:

S1625.pngS1624.pngS1623.png