Excel VBA: How to Unfilter Only One Autofilter Range at a Time? Code Provided

Question

Thanks for coming to this thread.

What I have:

-A report with an autofilter on rows A:G

What I need:

-Circumstantial code that unfilters a specific column if there is a filter on it.

-Running my code below unfilters the entire range of A:G.

-In this instance, I only want "F" unfiltered, leaving any other filters alone if they are filtered.

With Sheets("DATA")
    If .Range("F1").AutoFilter = True Then
        ActiveSheet.Range("$A$1:$G$59826").AutoFilter Field:=6
    Else
    End If
End With

Any and all ideas are greatly appreciated! Thank you so much!

Answer 1

Try this:

Sub UnFilter()
Dim ws As Excel.Worksheet

Set ws = Worksheets("DATA")
With ws
    If .AutoFilterMode = True Then
        If Not Intersect(.AutoFilter.Range, .Range("G1")) Is Nothing Then
            .Range("$A$1:$G$59826").AutoFilter Field:=.Range("G:G").Column
        End If
    End If
End With
End Sub

This line in your code:

If .Range("F1").AutoFilter = True

... actually turns off the filtering for the whole sheet. Instead my code checks if the sheet is filtered with:

If .AutoFilterMode = True Then

It then checks if the filter includes column G with:

If Not Intersect(.AutoFilter.Range, .Range("G1")) Is Nothing Then

I made a couple of changes to make your code a little more flexible. It also enables Intellisense for the ws object, which is helpful. (I always find the various Filter-related properties and methods confusing, especially without auto-completion.)