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!

Hubble

Your code looks fine to me,I also tested it and when I try it it does exactly as you ask for, removes autofilter from the single field. – Magi

When I run the code it removes the autofilter entirely. – Kylix

However, it also removes other fields that have already been filtered. So the autofilter will be reset in Columns B and C if they are filtered. I actually answered my own question, but I dont have enough rep to answer it. Heres the code:Addr = Split(ActiveSheet.UsedRange.Address, "$") lastRow = Addr(UBound(Addr)) For r = lastRow To 1 Step -1 If Len(Cells(r, "A").Value) Then lastRow = r Exit For End If Next ActiveSheet.Range("$A$1:$G" & lastRow).AutoFilter Field:=6 – Hubble

As a side note, if any answers, past or present, have answered your question, be sure to accept them by clicking the checkmark next to them. It's the SO way :). – Swellfish

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

Swellfish

Answer 2

This worked for me

Sub UnfilterColumn()

    With Worksheets("DATA")
        If Not Worksheets("DATA").AutoFilter Is Nothing Then
                ThisWorkbook.Sheets("DATA").Range("A1").AutoFilter Field:=6
        End If
    End With

End Sub

Kylix

It doesn't work for me. Nothing changes when it runs. I have a filter on another column and on G. – Swellfish

If the filter you want to remove is on column G, then use Field:=7 – Kylix

Oops, right you are, I had it on 6 still. However, it still fails if the filter is on another part of the sheet, e.g., column I. Then it passes the Not is Nothing test, but has an Error 1004 on the Field:=6 line` – Swellfish

Huh. If I filter on columns 1, 2 and 6, this code removes the filter on column 6, leaving filters on 1 and 2 intact. It also works for me if I don't have a filter on column 6 to start. I'm using Excel 2010; I wonder if that makes a difference. – Kylix

What it you only have a filter on column I? – Swellfish

BTW Doug, I recognised your name from the microsoft.public.excel.programming group - nice to see some of the old crowd in SO. – Kylix

You're right, it doesn't work if a range that doesn't include column 6 is filtered.Your intersect code will take care of that, I see – Kylix

SO has filled the hole that microsoft.public.excel.programming group left, and then some. Thanks for remembering! – Swellfish

Answer 3

I just entered the simple code below in the module and it worked fine for me. You will want to alter your data range and field to match your data. I'm clearing a filter with data in columns A through M. Field 8 is column H.

ActiveSheet.Range("$A:$M").AutoFilter Field:=8

Instalment