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
