Forum Discussion
Filter Non-Adjacent Columns
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 HansVogelaar, 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.
Yes: instead of ActiveSheet.ListObjects("Table1").Range specify the range it applies to, for example Range("A1:T100")
That's great HansVogelaar, 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.
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: