Opposite Filter for 2007

[schielrn]

This may or may not be the best way to handle this, but it surely was nice for Excel allowing us to select multiple criteria in an autofilter. But what would have been great would to have it filter on the opposite very simply by pushing a button or something.

Say you have a column like:

North
North
South
East
South
West
West
North

You then filter on north and south. It would be nice to push a button and filter on east and west and not north and south for a lot of things I work on. I know I could add a column that separated the 2, but that is not my idea.

So I have came up with this code that works only with the active column because I do not know how to determine which filter columns have criteria on them. It also will not work if other columns are filtered. It only works with one filtered column with the way I am building the array based on hidden rows.

I was trying to expand on this code here that worked with pre-2007 Excel:

http://www.j-walk.com/SS/excel/usertips/tip044.htm

But I have been struggling getting any results with that. It would be nice to be able to capture the array of variables that autofilter is using for the criteria, such as this is just recording a basic macro:

    ActiveSheet.Range("$A$1:$E$14").AutoFilter Field:=1, Criteria1:=Array("df", _
        "dsfg", "gds", "gsd", "sfd"), Operator:=xlFilterValues

I would love to be able to just capture criteria1 an dmaybe do something like "<>" or taking those out of my full array.

Sub OppositeFilter()
    Dim arr() As String
    For Each c In ActiveSheet.AutoFilter.Range.Resize(, 1).Offset(, ActiveCell.Column - 1)
        On Error Resume Next
        If c.EntireRow.Hidden Then
            If Not WorksheetFunction.Match(c.Value, arr(), 0) > 0 Then
                ReDim Preserve arr(counter)
                arr(counter) = c.Value
                counter = counter + 1
            End If
        End If
    Next c
    If counter = 0 Then Exit Sub
    ActiveSheet.AutoFilter.Range.AutoFilter Field:=ActiveCell.Column, Criteria1:=arr(), Operator:=xlFilterValues
End Sub

Basically it is looking at the column of the activecell to know what to apply the macro to. Has anyone tried working with this or any criticism or better approaches would be appreciated.

Thanks,

Rob

 

[schielrn]

Also, is there a way to test if a certain column is filtered?

Say Columns A, B, C & D have autofilter on them. And C is filtered on a certain criteria. Is there a way to know that C is the column that is filtered on because this only works on a sheet as a whole:

Activesheet.filtermode

And I cannot do that for a range.

I changed the code slightly so it doesn't have to go through the loop if the sheet is not filtered:

Sub OppositeFilter()
    Dim arr() As String
    If ActiveSheet.FilterMode = False Then Exit Sub
    For Each c In ActiveSheet.AutoFilter.Range.Resize(, 1).Offset(, ActiveCell.Column - 1)
        On Error Resume Next
        If c.EntireRow.Hidden Then
            If Not WorksheetFunction.Match(c.Value, arr(), 0) > 0 Then
                ReDim Preserve arr(counter)
                arr(counter) = c.Value
                counter = counter + 1
            End If
        End If
    Next c
    ActiveSheet.AutoFilter.Range.AutoFilter Field:=ActiveCell.Column, Criteria1:=arr(), Operator:=xlFilterValues
End Sub

 

[Richard Schollar]

Hi Rob

Regarding second point (how do you know if a column is filtered - at least I have assumed that's what you are asking ) you need to establish if the worksheet has autofilter activated and then I believe you need to loop thru the fields of the Autofilter.Range and test the Filters collection for being On. There may be a better way of doing this.

 

[schielrn]

Ok that has helped a lot Richard and I think I am getting a lot closer, now the only problem is matching the values in a column up against the criteria array.

Since this page doesn't have html as an option here is the URL to my Locals Window:

http://farm4.static.flickr.com/3220/2947207951_573d221814_o.jpg

I can set it as a variant array, but I cannot perform a match function against it. I could match in a loop by going:

if c.value = c1(i)

But there is no way of me finding out how many items are in c1. Also I'm not sure why I can't just use Criteria1(1)?

Somehow I'd like to insert something like:

if Not WorksheetFunction.Match("=" & c.Value, c1(), 0) > 0

But this is my code that is not quite working as I want at the moment because it does match up against c1.

Option Base 1
Sub OppositeFilter()
    Dim arr() As String
    If ActiveSheet.FilterMode = False Then Exit Sub
    For Each f In ActiveSheet.AutoFilter.Filters
    counter = counter + 1
        If f.On = True Then
            c1 = f.Criteria1
            For Each c In f.Parent.Range.Offset(1, counter - 1).Resize(f.Parent.Range.Offset(1, counter - 1).Resize(, 1).Count - 1, 1)
                On Error Resume Next
                If Not WorksheetFunction.Match("=" & c.Value, arr(), 0) > 0 Then
                    counter2 = counter2 + 1
                    ReDim Preserve arr(counter2)
                    arr(counter2) = "=" & c.Value
                End If
            Next c
            ActiveSheet.AutoFilter.Range.AutoFilter Field:=counter, Criteria1:=arr(), Operator:=xlFilterValues
        End If
    Next f
End Sub

I could go back to looking at hidden rows again, but that only allows for one column to be swapped. Your filters collection approach has helped me find the column(s) that are filtered.

Thanks for your help so far and any more help anyone can give me.

 

[schielrn]

Also I believe my for each statement can be reduced, but hey it works.

It basically first returns the filterrange, in an example:

A1:E16

I basically only want if filter on column A is "on" then I need to loop through A2:A16 and that is what my long code gives me.

 

[Richard Schollar]

Rob

Not specifically using Autofilter, but check out the ColumnDifferences method of the Range object (definitely works in 2007) for a way to get does not equal. Would probably require you to have a cell that contains the does not equal criteria though. This technique was highlighted to me tonight by Tom Urtis.