Here’s another in what’s turning out to be a series of posts on AutoFiltering. This time it’s a bit of code to remove filters from all the columns except those that are part of the current selection. The idea is kind of like “Close Other Tabs” in Fireox or Notepad++. It works for Tables and plain old filtered ranges. I wrote it the other day while working on a mile-wide list with various fancy filters, most of which I wanted to get rid of.
It’s not much code, and hopefully it’s self-explanatory. (If not, you know where to find me.)
Dim i As Long
Dim AutoFltr As Excel.AutoFilter
If ActiveSheet Is Nothing Then
MsgBox "No active worksheet."
Exit Sub
End If
'Determine the range with the filter depending on whether it's a Table or not
If Not ActiveCell.ListObject Is Nothing Then
Set AutoFltr = ActiveCell.ListObject.AutoFilter
Else
Set AutoFltr = ActiveCell.Parent.AutoFilter
End If
If AutoFltr Is Nothing Then
MsgBox "The selection is not within filtered table or range"
Exit Sub
End If
If Union(Selection, AutoFltr.Range).Address <> AutoFltr.Range.Address Then
MsgBox "Please make sure all cells are within the same table or filtered area."
Exit Sub
End If
For i = 1 To AutoFltr.Filters.Count
If AutoFltr.Filters(i).On And _
(Intersect(Selection.EntireColumn, AutoFltr.Range.Columns(i)) Is Nothing) Then
AutoFltr.Range.AutoFilter Field:=i
End If
Next i
End Sub
The part I like is how it narrows down to a aingle AutoFilter object regardless of whether you’re working with a ListObject or just a filtered range. I’m not sure I knew there was such an object until I read Mike Alexander’s post on A Better Way to Copy Filtered Rows Using VBA.
Pingback: Excel Roundup 20151005 « Contextures Blog
That is an amazing little piece of code you have kindly shared with us.
It is now in my PERSONAL.XLSB (credited to this page of course) and listed on my custom ribbon tab.
Thanks too to Debra @ Contextures (http://blog.contextures.com/archives/2015/10/05/excel-roundup-20151005/) for directing me to it.
Cheers,
Darren