I have an Autofilter. I want to be able to toggle the current filter on and
off. I already have a mechanism in place that does this. however, it does
not work when one of the columns is filtered for "Blanks" or "nonblanks". A
workaround is to explicitly custom filter for equal "" or not equal ""...
but it seems a little roundabout. I have not found any help in the object
browser or the Help files.

Any help would be appreciated. Thanks!

What do you mean by does not work.

Selection.AutoFilter

will toggle the autofilter on and off

if you mean showall, it should work as well if the datafilter is actually
on.

Show the code that isn't working. You experience is atypical.

--
Regards,
Tom Ogilvy

"R Avery" wrote in message
...
I have an Autofilter. I want to be able to toggle the current filter on
and
off. I already have a mechanism in place that does this. however, it
does
not work when one of the columns is filtered for "Blanks" or "nonblanks".
A
workaround is to explicitly custom filter for equal "" or not equal ""...
but it seems a little roundabout. I have not found any help in the object
browser or the Help files.

Any help would be appreciated. Thanks!

I have tried to do what you say, and Selection.Autofilter does remove all
filters, but I need to save the current filters in a Worksheet property so
that i can reinstate them later.

The problem is that the way Excel's Filter object records a "Non-blanks"
filter is "". When I later reapply this criteria on the filter range, no
cells come up. I guess I could loop through all items whose criteria is ""
and replace it with something else, but I am looking for a built-in Excel
way.

To replicate my problem, create a worksheet with a table, and fill it with
dummy data. Add the code at the end of this post (modified from the Excel
HELP topics relating to autofilters) to the sheet module. Add two buttons
to the sheet, one of which calls "RemoveAllFiltersHoldings" and another
which calls "RestoreFilters". Create a named range called "AllData" that
refers to your table.

Autofilter the list in one column by non-blank (make sure that there are
both blank and non-blank cells in that field, just because... Then, click
the remove filters button. This saves a list of all criteria in the
autofilter. Then click the restore filters button. This will iterate
through the saved criteria and re-instate them in the autofilter. The
problem is that you will no longer see any data, whereas before this
exercise you did. I believe the reason is because Excel does not know how
to record "non-blanks" as a criteria properly.

Thanks.



The code I am using:

Private filterArray() As Variant

Public Sub RemoveAllFiltersHoldings()
If Me.FilterMode Then
Me.GetFilterList
Me.ShowAllData
End If
End Sub


Public Sub GetFilterList()
Dim f As Filter, rw As Long, c1, c2, op

rw = 1

ReDim filterArray(1 To Range("AllData").Columns.Count, 1 To 3)

If Me.FilterMode Then
For Each f In Me.AutoFilter.Filters
c1 = Empty
op = Empty
c2 = Empty
If f.On Then
c1 = Right(f.Criteria1, Len(f.Criteria1) - 1)
If f.Operator Then
op = f.Operator
c2 = Right(f.Criteria2, Len(f.Criteria2) - 1)
End If
End If
If Not IsEmpty(c1) Then filterArray(rw, 1) = c1
If Not IsEmpty(op) Then filterArray(rw, 2) = op
If Not IsEmpty(c2) Then filterArray(rw, 3) = c2
rw = rw + 1
Next
End If
End Sub


Public Sub RestoreFilters()
'Me.AutoFilterMode = False
Application.ScreenUpdating = False
currentFiltRange = "AllData"
For col = 1 To UBound(filterArray(), 1)
If Not IsEmpty(filterArray(col, 1)) Then
If Not IsEmpty(filterArray(col, 2)) Then
Me.Range(currentFiltRange).AutoFilter field:=col, _
Criteria1:=filterArray(col, 1), _
Operator:=filterArray(col, 2), _
Criteria2:=filterArray(col, 3)
Else
Me.Range(currentFiltRange).AutoFilter field:=col, _
Criteria1:=filterArray(col, 1)
End If
End If
Next
Application.ScreenUpdating = True
End Sub

That's it! Thanks.


"Tom Ogilvy" wrote in message
...
I don't know why they are removing one character from the left of the
criteria, but it you don't, it worked with all the different criteria's I
tried including blanks and non-blanks. I just changed the -1 to -0 so
you
can change it back if you find a reason that it should be that way.

c1 = Right(f.Criteria1, Len(f.Criteria1) - 1)
If f.Operator Then
op = f.Operator
c2 = Right(f.Criteria2, Len(f.Criteria2) - 1)

changed to

c1 = Right(f.Criteria1, Len(f.Criteria1) - 0)
If f.Operator Then
op = f.Operator
c2 = Right(f.Criteria2, Len(f.Criteria2) - 0)

Non-blanks is actually < the you talk about is the result of
stripping
off the < from < and then when you put it back, only remains.

--
Regards,
Tom Ogilvy



R Avery wrote in message
...
I have tried to do what you say, and Selection.Autofilter does remove
all
filters, but I need to save the current filters in a Worksheet property
so
that i can reinstate them later.

The problem is that the way Excel's Filter object records a "Non-blanks"
filter is "". When I later reapply this criteria on the filter range,
no
cells come up. I guess I could loop through all items whose criteria is
""
and replace it with something else, but I am looking for a built-in
Excel
way.

To replicate my problem, create a worksheet with a table, and fill it
with
dummy data. Add the code at the end of this post (modified from the
Excel
HELP topics relating to autofilters) to the sheet module. Add two
buttons
to the sheet, one of which calls "RemoveAllFiltersHoldings" and another
which calls "RestoreFilters". Create a named range called "AllData"
that
refers to your table.

Autofilter the list in one column by non-blank (make sure that there are
both blank and non-blank cells in that field, just because... Then,
click
the remove filters button. This saves a list of all criteria in the
autofilter. Then click the restore filters button. This will iterate
through the saved criteria and re-instate them in the autofilter. The
problem is that you will no longer see any data, whereas before this
exercise you did. I believe the reason is because Excel does not know
how
to record "non-blanks" as a criteria properly.

Thanks.



The code I am using:

Private filterArray() As Variant

Public Sub RemoveAllFiltersHoldings()
If Me.FilterMode Then
Me.GetFilterList
Me.ShowAllData
End If
End Sub


Public Sub GetFilterList()
Dim f As Filter, rw As Long, c1, c2, op

rw = 1

ReDim filterArray(1 To Range("AllData").Columns.Count, 1 To 3)

If Me.FilterMode Then
For Each f In Me.AutoFilter.Filters
c1 = Empty
op = Empty
c2 = Empty
If f.On Then
c1 = Right(f.Criteria1, Len(f.Criteria1) - 1)
If f.Operator Then
op = f.Operator
c2 = Right(f.Criteria2, Len(f.Criteria2) - 1)
End If
End If
If Not IsEmpty(c1) Then filterArray(rw, 1) = c1
If Not IsEmpty(op) Then filterArray(rw, 2) = op
If Not IsEmpty(c2) Then filterArray(rw, 3) = c2
rw = rw + 1
Next
End If
End Sub


Public Sub RestoreFilters()
'Me.AutoFilterMode = False
Application.ScreenUpdating = False
currentFiltRange = "AllData"
For col = 1 To UBound(filterArray(), 1)
If Not IsEmpty(filterArray(col, 1)) Then
If Not IsEmpty(filterArray(col, 2)) Then
Me.Range(currentFiltRange).AutoFilter field:=col, _
Criteria1:=filterArray(col, 1), _
Operator:=filterArray(col, 2), _
Criteria2:=filterArray(col, 3)
Else
Me.Range(currentFiltRange).AutoFilter field:=col, _
Criteria1:=filterArray(col, 1)
End If
End If
Next
Application.ScreenUpdating = True
End Sub