"saving" autofilter criteria to be reapplioed later in macro

[kuraitori]

Hello all,
I have an autofilter applied to 5 columns. Now I need to remove it for part of the macro but I want to "save" the autofilter criteria so that I can reapply it at the end of the macro

something like

dim crit1 as string
crit1 = autofilter.Field("1").Criteria("1").Value
myrange.autofilter

'code that requires autofilter off runs here

'autofilter gets reapplied
myrange.autofilter
autofilter field:= 1 criteria1:= crit1

except I have no idea about proper syntax.

Any ideas?

[rylo]

Hi

Have a look at the autofilter object. There is some example code that should help you. The code below is based on that...

Sub bbba()
  Dim filterArray()
  With Sheets("Sheet1").AutoFilter.Filters
    ReDim filterArray(1 To .Count, 1 To 3)
    For i = 1 To .Count
      If .Item(i).On Then
        filterArray(i, 1) = .Item(i).Criteria1
        If .Item(i).Operator Then
          filterArray(i, 2) = .Item(i).Operator
          filterArray(i, 3) = .Item(i).Criteria2
        End If
      End If
    Next i
    
  End With
  For i = 1 To UBound(filterArray)
    For J = 1 To 3
      If filterArray(i, J) <> "" Then
        Debug.Print i & ", " & J & ", " & filterArray(i, J)
      End If
    Next J
  Next i
End Sub

rylo

[FWB]

I just stumbled across this post, in looking for a solution for something else.
That concept fails if .Operator := [Top|Bottom]10[Items|Percent].
In this case, the result returned for .Criteria1 is an absolute compare|match (i.e. ">=255", rather than the item count/percent.
Re-applying that as the Criteria for an Autofilter causes a fatal error.