Remove Filters From Other Columns

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.)

Sub ClearFiltersFromOtherColumns()
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.

2 thoughts on “Remove Filters From Other Columns

  1. Pingback: Excel Roundup 20151005 « Contextures Blog

Speak Your Mind

Your email address will not be published. Required fields are marked *

To post code, do this: <code> your vba here </code>