none
Determine Filter Conditions, save and reinstate RRS feed

  • Question

  • Windows 10 and Office 2016

    I have a number of Excel VBA procedures that have to turn off Autofilter before they run.  I would like to write a procedure that records all the conditions, stores them. A further procedure will reinstate Autofilter with the original conditions when the main procedure is complete.  My first thought is to create a three dimensional array to store the column, conditions and advanced options for each column that has Autofilter applied, then reapply them at the end of the procedure. 

    Two questions:  First, can anyone advise me whether this is the appropriate approach, since it seems rather long-winded? 

    Second, I don't wish to re-invent the wheel, so is anyone aware of a similar procedure that already exists, or on which I can draw to create my own code?

    With thanks

    Andy C

    EDIT added 8 March 2016

    I should point out that the MSDN Office Dev Center on the AutoFilter Object (not Property) (reached by pressing F1 in the VBE with "AutoFilter" selected) shows code for this problem, but it produces an error when more than two filter criteria are selected, when Criteria1 becomes an array.  ReDim for that situation has proved beyond my abilities so far.

    • Edited by AndyColRomsey Tuesday, March 8, 2016 10:10 AM New Info
    Thursday, February 25, 2016 9:55 AM

Answers

  • Hi Andy,

    Could it be because you are trying to assign a value to AutoFilter .Criteria2 when you have assigned an array ov values to .Criteria1? What if you try something like the following...

          If IsArray(filterArray(col, 1)) Then
             'Only assign to .Criteria1
          Else
             'Assign to .Criteria1 and .Criteria2
          End If

    - Richard W

    • Marked as answer by AndyColRomsey Wednesday, March 9, 2016 3:31 PM
    Wednesday, March 9, 2016 1:17 PM

All replies

  • Hi, AndyColRomsey

    I am looking into your issue and when I find a suitable solution for you. I will provide you.

    Regards

    Deepak

    Friday, February 26, 2016 10:12 AM
    Moderator
  • Deepak, many thanks

    Andy C

    Friday, February 26, 2016 10:50 AM
  • Hi, AndyColRomsey

    you mean that you want information regarding AutoFilter Object not regarding AutoFilter Property.

    here is the link for Autofilter object.

    Auto Filter Object

    the other thing you have mentioned that you are getting an error when you select more then 2 criteria and when criteria 1 is array.

    so can you provide your code and detailed steps to reproduce this issue.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place. <br/> Click <a href="http://support.microsoft.com/common/survey.aspx?showpage=1&scid=sw%3Ben%3B3559&theme=tech"> HERE</a> to participate the survey.

    Wednesday, March 9, 2016 2:03 AM
    Moderator
  • Deepak

    In my 8 March edit to my original query I said that I have seen the link that you provided in your reply today.  It is the code in that link that generates an error when it is run with a situation with three or more filters on a single column. So if I create a workbook with a sheet named "Crew", and have in column A:

    Header

    1

    2

    3

    4

    5

    The following code, (which includes the two examples from the MSDN page on AutoFilter Object), produces an error at the line   filterArray(f, 3) = .Criteria2   in ChangeFilters() if I have filters applied to "1", "2", and "3".  It works OK if I have filters only applied to "1", and "2".

    Option Explicit

    Dim f As Integer
    Dim w As Worksheet
    Dim filterArray()
    Dim currentFiltRange As String
    Dim col As Integer

    Sub TestFilters()

    ChangeFilters
    MsgBox "Change Filters is Complete"
    RestoreFilters

    End Sub

     
    Sub ChangeFilters()
     
    Set w = Worksheets("Crew")
    With w.AutoFilter
     currentFiltRange = .Range.Address
     With .Filters
     ReDim filterArray(1 To .Count, 1 To 3)
     For f = 1 To .Count
     With .Item(f)
     If .On Then
     filterArray(f, 1) = .Criteria1
     If .Operator Then
     filterArray(f, 2) = .Operator
     filterArray(f, 3) = .Criteria2
     End If
     End If
     End With
     Next
     End With
    End With
     
    w.AutoFilterMode = False

     
    End Sub

    Sub RestoreFilters()
    Set w = Worksheets("Crew")
    w.AutoFilterMode = False
    For col = 1 To UBound(filterArray(), 1)
     If Not IsEmpty(filterArray(col, 1)) Then
     If filterArray(col, 2) Then
     w.Range(currentFiltRange).AutoFilter field:=col, _
     Criteria1:=filterArray(col, 1), _
     Operator:=filterArray(col, 2), _
     Criteria2:=filterArray(col, 3)
     Else
     w.Range(currentFiltRange).AutoFilter field:=col, _
     Criteria1:=filterArray(col, 1)
     End If
     End If
    Next
    End Sub

    With thanks

    Andy C

    Wednesday, March 9, 2016 8:40 AM
  • Hi, AndyColRomsey

    I try to execute above mentioned vba code and its working correctly. I did not get any errors. can you tell us which error you get?

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place. <br/> Click <a href="http://support.microsoft.com/common/survey.aspx?showpage=1&scid=sw%3Ben%3B3559&theme=tech"> HERE</a> to participate the survey.

    Wednesday, March 9, 2016 10:23 AM
    Moderator
  • Hi Deepak

    I get Run-time error '1004', Application-defined or object-defined error on the line   filterArray(f, 3) = .Criteria2  in  Sub ChangeFilters().  I emphasise that the error only occurs if three or more filters are applied to a column.  The code runs as expected when only one or two filters are applied in column A.

    Andy C

    Wednesday, March 9, 2016 10:40 AM
  • Hi Andy,

    Could it be because you are trying to assign a value to AutoFilter .Criteria2 when you have assigned an array ov values to .Criteria1? What if you try something like the following...

          If IsArray(filterArray(col, 1)) Then
             'Only assign to .Criteria1
          Else
             'Assign to .Criteria1 and .Criteria2
          End If

    - Richard W

    • Marked as answer by AndyColRomsey Wednesday, March 9, 2016 3:31 PM
    Wednesday, March 9, 2016 1:17 PM
  • Hi Richard

    Thanks, I will try something along those lines.  The confusion really arises because of the use of two criteria for the one or two filter situation, but of an array for Criteria1 for more than two filters.

    Andy C

    Wednesday, March 9, 2016 2:28 PM
  • Hi Richard

    Your suggestion put me straight onto the right lines, and I now have working procedures to clear then reinstate filters.

    With thanks

    Andy C

    Sub CallFilters2()
    'Based on Help (F1) on AutoFilter (object) in VBE at https://msdn.microsoft.com/en-us/library/office/ff194617(v=office.15).aspx _

        ChangeFilters2
        MsgBox "Filter Change Complete"
        RestoreFilters2
       
    End Sub

    Sub ChangeFilters2()
     
    Set w = Worksheets("Filters")
    With w.AutoFilter
     currentFiltRange = .Range.Address
     With .Filters
     ReDim filterArray(1 To .Count, 1 To 3)
     For f = 1 To .Count
     With .Item(f)
     If .On Then
     filterArray(f, 1) = .Criteria1
     If .Operator Then
     filterArray(f, 2) = .Operator
     If IsArray(.Criteria1) = False Then
        filterArray(f, 3) = .Criteria2
     End If
     End If
     End If
     End With
     Next
     End With
    End With
     
    w.AutoFilterMode = False
     
    End Sub

    Sub RestoreFilters2()
    Set w = Worksheets("Filters")
    w.AutoFilterMode = False
    For col = 1 To UBound(filterArray(), 1)
     If Not IsEmpty(filterArray(col, 1)) Then
     If filterArray(col, 2) Then
     w.Range(currentFiltRange).AutoFilter Field:=col, _
     Criteria1:=filterArray(col, 1), _
     Operator:=filterArray(col, 2), _
     Criteria2:=filterArray(col, 3)
     Else
     w.Range(currentFiltRange).AutoFilter Field:=col, _
     Criteria1:=filterArray(col, 1)
     End If
     End If
    Next
    End Sub

    Wednesday, March 9, 2016 3:34 PM
  • Good to hear that it's working.

    Another useful consideration: If using the AutoFilter of a Worksheet, then you can have only one AutoFilter on that Worksheet. However, one of the benefits of defining Tables (Insert ribbon >> Table) is that you can have multiple list objects on the one sheet, each able to do its own filtering, sorting, etc. Your code would then reference the AutoFilter of the respective ListObject, instead of the worksheet's AutoFilter.

    Eg:

       Dim objList As ListObject  
       Set objList = [MyTableName].ListObject
       With objList.AutoFilter
          'Measure and/or set the filtering.
       End With
      

    Thursday, March 10, 2016 11:51 AM
  • Thanks Richard; a useful point, though not applicable to my current project. Worth considering for future projects, no doubt.

    Andy C

    Thursday, March 10, 2016 12:38 PM