none
Generate Autofilter Criteria1 Array as a variable RRS feed

  • Question

  • Using Windows 10 and Office 2016. 

    I need to apply a filter to a column based on the contents of another range in a second worksheet.  I have generated a Variant (string) that appears to meet the requirement, but it filters out everything.  When I put into Criteria1 (Array) what appears to be identical to the contents of the string, the filter is correctly applied.  The range to be filtered in a tab named "Filters" looks like this:

    and the tab containing the data for the array, named "TempSheet" looks like this:

    Example code (an extract from a larger procedure) is as follows:

    Sub LoadFilters()
    Dim j As Integer
    Dim varArrayString As Variant

    Worksheets("TempSheet").Activate

        If Worksheets("TempSheet").Cells(4, 1).Value <> "" Then
            varArrayString = ""
            For j = 2 To Cells(Rows.Count, 1).End(xlUp).Row
                If j = 2 Then
                    varArrayString = varArrayString & Chr(34) & Cells(j, 1).Value & Chr(34)
               
                Else
                    varArrayString = varArrayString & ", " & Chr(34) & Cells(j, 1).Value & Chr(34)
                End If
            Next j
            varArrayString = Chr(34) & varArrayString & Chr(34)
        Worksheets("Filters").Activate
        Range("A1").Select
       
        Selection.AutoFilter Field:=Worksheets("TempSheet").Cells(1, 1).Value, _
        Criteria1:=Array(varArrayString), Operator:=xlFilterValues

    IF I CHANGE THE TWO LINES OF CODE ABOVE TO THIS - IT WORKS

    '    Selection.AutoFilter Field:=Worksheets("TempSheet").Cells(1, 1).Value, _
    '    Criteria1:=Array("abc", "abd", "abe", "abf"), Operator:=xlFilterValues
       

        ElseIf Worksheets("TempSheet").Cells(3, 1).Value <> "" Then
            Selection.AutoFilter Field:=Worksheets("TempSheet").Cells(1, 1).Value, _
                Criteria1:=Worksheets("TempSheet").Cells(2, 1).Value, Operator:=xlOr, Criteria2:=Cells(3, 1).Value
               
        ElseIf Worksheets("TempSheet").Cells(3, 1) = "" Then
            Selection.AutoFilter Field:=Cells(1, 1).Value, _
                Criteria1:=Cells(2, 1).Value
        End If

    End Sub

    When I put into the immediate window   ?varArrayStiring  , it looks identical to what I have in the Criteria1:=(Array) in the version that works, as it also does in the Locals window.  No error messages are generated.  It simply filters out everything.  Can anyone please guide me to getting a value of VarArrayString that will actually work?

    with thanks

    AndyC

    Sunday, March 6, 2016 11:54 AM

Answers


  •     Worksheets("Filters").Activate
        Range("A1").Select
       
        Selection.AutoFilter Field:=Worksheets("TempSheet").Cells(1, 1).Value, _
        Criteria1:=Array(varArrayString), Operator:=xlFilterValues

    IF I CHANGE THE TWO LINES OF CODE ABOVE TO THIS - IT WORKS

    '    Selection.AutoFilter Field:=Worksheets("TempSheet").Cells(1, 1).Value, _
    '    Criteria1:=Array("abc", "abd", "abe", "abf"), Operator:=xlFilterValues

    Please never use SELECT, SELECTION, ACTIVECELL, it is slow and error prone. Always refer to the objects directly.

    Array(varArrayString) is a one dimensional array with one element (a string that contains commas).

    Array("abc", "abd", "abe", "abf")  is a one dimensional array with 4 elements (strings).

    Andreas.

    Sub LoadFilters()
      Dim MyArray As Variant, MyField As Long
    
      With Worksheets("TempSheet")
        'Load the values into a 2D array
        MyArray = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
        MyField = .Range("A1")
      End With
      With WorksheetFunction
        'Tranpose to a 1D array
        MyArray = .Transpose(MyArray)
      End With
    
      Worksheets("Filters").Range("A1").AutoFilter Field:=MyField, _
          Criteria1:=MyArray, Operator:=xlFilterValues
    End Sub
    

    • Marked as answer by AndyColRomsey Sunday, March 6, 2016 4:42 PM
    Sunday, March 6, 2016 4:07 PM
  • Sub LoadFilters()
      Dim MyArray As Variant, MyField As Long
      Dim i As Long
    
      With Worksheets("TempSheet")
        'Load the values into a 2D array
        MyArray = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
        MyField = .Range("A1")
      End With
      With WorksheetFunction
        'Tranpose to a 1D array
        MyArray = .Transpose(MyArray)
      End With
      
      'The elements in the array must be a string
      For i = LBound(MyArray) To UBound(MyArray)
        MyArray(i) = CStr(MyArray(i))
      Next
    
      Worksheets("Filter").Range("A1").AutoFilter Field:=MyField, _
        Criteria1:=MyArray, Operator:=xlFilterValues
    End Sub
    

    • Marked as answer by AndyColRomsey Monday, March 7, 2016 7:08 PM
    Monday, March 7, 2016 5:25 PM

All replies


  •     Worksheets("Filters").Activate
        Range("A1").Select
       
        Selection.AutoFilter Field:=Worksheets("TempSheet").Cells(1, 1).Value, _
        Criteria1:=Array(varArrayString), Operator:=xlFilterValues

    IF I CHANGE THE TWO LINES OF CODE ABOVE TO THIS - IT WORKS

    '    Selection.AutoFilter Field:=Worksheets("TempSheet").Cells(1, 1).Value, _
    '    Criteria1:=Array("abc", "abd", "abe", "abf"), Operator:=xlFilterValues

    Please never use SELECT, SELECTION, ACTIVECELL, it is slow and error prone. Always refer to the objects directly.

    Array(varArrayString) is a one dimensional array with one element (a string that contains commas).

    Array("abc", "abd", "abe", "abf")  is a one dimensional array with 4 elements (strings).

    Andreas.

    Sub LoadFilters()
      Dim MyArray As Variant, MyField As Long
    
      With Worksheets("TempSheet")
        'Load the values into a 2D array
        MyArray = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
        MyField = .Range("A1")
      End With
      With WorksheetFunction
        'Tranpose to a 1D array
        MyArray = .Transpose(MyArray)
      End With
    
      Worksheets("Filters").Range("A1").AutoFilter Field:=MyField, _
          Criteria1:=MyArray, Operator:=xlFilterValues
    End Sub
    

    • Marked as answer by AndyColRomsey Sunday, March 6, 2016 4:42 PM
    Sunday, March 6, 2016 4:07 PM
  • Andreas

    Many thanks; that works and I can adapt it to my project easily.

    I take your point about direct reference to objects, and indeed generally try to write my code that way.

    AndyC

    Sunday, March 6, 2016 4:45 PM
  • Andreas

    I have a further problem with  the code you provided.  I works well when the data in "Filters" and "TempSheet" are strings, but when they are numbers MyArray type is shown as Variant / Double, and everything is filtered out. I used "Title A" and digits 1 to 6 in column A of "Filters" and "1" and digits 1, 2 and 4 in Column A of "TempSheet".  I have tried Cstr on the variants (without much hope) and got a error.  I have not yet tried with decimal numbers, nor currency nor dates.

    I get no error message;  the procedure simply unticks all the options in the filter Select dropdown. Have you any further guidance?

    With thanks

    AndyC

    Monday, March 7, 2016 4:06 PM
  • Andy,

    Please upload your file on an online file hoster like www.dropbox.com and post the download link here. I'll take a look.

    Andreas.

    Monday, March 7, 2016 4:19 PM
  • Andreas

    Many thanks for  looking at this.

    The file is at

    https://dl.dropboxusercontent.com/u/51949344/FilterTest.xlsm

    The Macro is "LoadFilters"

    AndyC

    Monday, March 7, 2016 5:16 PM
  • Sub LoadFilters()
      Dim MyArray As Variant, MyField As Long
      Dim i As Long
    
      With Worksheets("TempSheet")
        'Load the values into a 2D array
        MyArray = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
        MyField = .Range("A1")
      End With
      With WorksheetFunction
        'Tranpose to a 1D array
        MyArray = .Transpose(MyArray)
      End With
      
      'The elements in the array must be a string
      For i = LBound(MyArray) To UBound(MyArray)
        MyArray(i) = CStr(MyArray(i))
      Next
    
      Worksheets("Filter").Range("A1").AutoFilter Field:=MyField, _
        Criteria1:=MyArray, Operator:=xlFilterValues
    End Sub
    

    • Marked as answer by AndyColRomsey Monday, March 7, 2016 7:08 PM
    Monday, March 7, 2016 5:25 PM
  • Andreas

    That was quick! Many thanks; I had tried to do the CStr on the individual elements of MyArray.

    Your help is much appreciated

    AndyC

    Monday, March 7, 2016 5:35 PM