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."
'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
Set AutoFltr = ActiveCell.Parent.AutoFilter
If AutoFltr Is Nothing Then
MsgBox "The selection is not within filtered table or range"
If Union(Selection, AutoFltr.Range).Address <> AutoFltr.Range.Address Then
MsgBox "Please make sure all cells are within the same table or filtered area."
For i = 1 To AutoFltr.Filters.Count
If AutoFltr.Filters(i).On And _
(Intersect(Selection.EntireColumn, AutoFltr.Range.Columns(i)) Is Nothing) Then
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.