How to transfer criteria from string array to autofilter. VBA Excel

excel vba filter multiple criteria array
excel vba advanced filter multiple criteria not equal
criteria1:=array
autofilter criteria cell value
excel vba autofilter criteria not equal to array
excel vba autofilter contains multiple criteria
excel vba filter multiple criteria different column
vba autofilter multiple criteria

I tried in many ways but none of them worked and what i'm trying to do is to transfer data from string array to autofilter criteria. Part of my code:

crit(21) = """audi"", ""mercedes"""

    Cells.Find(What:="Film", After:=ActiveCell,  LookIn:=xlFormulas,       LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False).Activate
    ActiveCell.Select
    Set zasieg = Range(ActiveCell, ActiveCell.Offset(0, 15))
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$P$200000").AutoFilter Field:=14, Criteria1:=Array(crit(21)), Operator:=xlFilterValues

This part of code works in the loop and some crit(i) have 5 elements.

Make an array out of the string and use the array:

Sub dural()
    Dim MyString As String, r As Range
    MyString = "Larry,Moe,Curly,Shepp"
    ary = Split(MyString, ",")
    Set r = Range("A1:A14")
    With r
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:=(ary), Operator:=xlFilterValues
    End With
End Sub

EDIT#1:

Here is the alternative code (which you should not use)

Sub DontUseThisCode()
    Dim MyString As String, r As Range
    Dim ary(0 To 3) As String

    ary(0) = "Larry"
    ary(1) = "Moe"
    ary(2) = "Curly"
    ary(3) = "Shepp"
    Set r = Range("A1:A14")
    With r
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:=(ary), Operator:=xlFilterValues
    End With

End Sub

You do not need to transpose a single element from an array and you cannot put criteria into the 5th field if you are only referencing column E. NOTE: If you leave out the Operator in Excel VBA Autofilter then only last value of the Array will be displayed. You can also pass the values directly like: WS.UsedRange.AutoFilter Field:=2, Criteria1:=Array("Apples","Peaches","Grapes), Operator:=xlFilterValues

An example showing how to build a filter from a range (converting range to an array) without loop and use the array to set the filter. This example uses two worksheets (Sheet1, Sheet2) - where Sheet1 is the main dataset being filtered. Sheet2 has the filter values.

Public Sub xFilter()
   Dim rng As Range
   Dim tmp As String
   Dim val()       
   Dim addr As String
   Dim intStart As Long
   Dim intEnd As Long
   Dim intCol As Integer
   Worksheets("Sheet2").Select ' A worksheet with the filter (possible) values.
   Range("$A$2").Select
   Range(Selection, Selection.End(xlDown)).Select
   Set rng = Selection ' get address
   intStart = rng.Row
   intEnd = intStart + (rng.Count - 1)
   intCol = rng.Column ' next column
   Set rng = Range(Cells(intStart, (intCol + 1)), Cells(intEnd, (intCol + 1)))
   ' convert to text
   rng.FormulaR1C1 = "=Text(rc[-1],0)"
   ' convert to array of text
   val = Application.Transpose(rng)
   rng.FormulaR1C1 = "" ' remove formula
   Set rng = Nothing
   Worksheets("Sheet1").Select
   Range("$A$1").Select
   Range(Selection, Selection.End(xlDown)).Select
   Set rng = Selection
   intStart = rng.Row
   intEnd = intStart + (rng.Count - 1)
   intCol = rng.Column ' next column
   Range(Cells(intStart, 1), Cells(intEnd, 2)).AutoFilter
   Range(Cells(intStart, 1), Cells(intEnd, 2)).AutoFilter _
        Field:=1, Criteria1:=val, Operator:=xlFilterValues
End Sub

See how to apply an Excel AutoFilter with multiple criteria in a range on the worksheet The criteria are entered as an array, showing all three items that were In an Excel VBA procedure, you can create a variable to store the values from I need to get a string listing the selected filter criteria for a column. Excel Autofilter to an Array with VBA. Recently on the Ozgrid I was involved in a discussion about adding data to an Array after it has been filtered. If I use the current region as the Array's contents, Excel will include all of the data including the hidden data in the Array.

It looks like this has been answered a while ago but I wanted to ask an additional question. Can you make this into a universal macro using some user input?

Public Sub xFilter()
   Dim rng As Range
   Dim tmp As String
   Dim val()
   Dim addr As String
   Dim intStart As Long
   Dim intEnd As Long
   Dim intCol As Integer
   Dim FilterLocation As Range
   Dim FilterValues As Range

   Set FilterLocation = Application.InputBox("Select Filter Location", "Obtain Range Object", Type:=8)
   Set FilterValues = Application.InputBox("Selection: Filter Criteria", "Obtain Range Object", Type:=8)


   Worksheets(FilterValues.Worksheet.Name).Select ' A worksheet with the filter (possible) values.
   FilterValues.Select
   Range(Selection, Selection.End(xlDown)).Select
   Set rng = Selection ' get address
   intStart = rng.Row
   intEnd = intStart + (rng.Count - 1)
   intCol = rng.Column ' next column
   Set rng = Range(Cells(intStart, (intCol + 1)), Cells(intEnd, (intCol + 1)))
   ' convert to text
   rng.FormulaR1C1 = "=Text(rc[-1],0)"
   ' convert to array of text
   val = Application.Transpose(rng)
   rng.FormulaR1C1 = "" ' remove formula
   Set rng = Nothing
   Worksheets(FilterLocation.Worksheet.Name).Select
   FilterLocation.Select
   Range(Selection, Selection.End(xlDown)).Select
   Set rng = Selection
   intStart = rng.Row
   intEnd = intStart + (rng.Count - 1)
   'intCol = rng.Column ' next column
   'Range(Cells(intStart, 1), Cells(intEnd, 2)).AutoFilter
   Range(Cells(intStart, 1), Cells(intEnd, 2)).AutoFilter _
        Field:=1, Criteria1:=val, Operator:=xlFilterValues
End Sub

The only issue is that when I substitute this for the last row I get an error:

Range(Cells(intStart, 1), Cells(intEnd, 2)).AutoFilter _
    Field:=FilterValues.Column, Criteria1:=val, Operator:=xlFilterValues

I tried in many ways but none of them worked and what i'm trying to do is to transfer data from string array to autofilter criteria. Part of my code: crit(21) = """​audi"",  Put that into an array and use that as the Criteriabut I can't figure out the syntax. Checked these, but still but can't make it work: AutoFilter Criteria Using Array (Error) - Too Large String? How do I autofilter using an array for criteria; How to transfer criteria from string array to autofilter. VBA Excel

VBA Autofilter Excel - syntax & Example Macros. Codes for Filtering data, ranges, rows, column criteria in Microsoft Excel 2007,2010,2013,2016. Helps to filter the array elements based on match string. Excel VBA Copy Range to Another Sheet with Formatting · VBA Filter Multiple Columns · VBA Filter  Excel VBA Autofilter Syntax Expression. AutoFilter( _Field_ , _Criteria1_ , _Operator_ , _Criteria2_ , _VisibleDropDown_ ) Expression: This is the range on which you want to apply the auto filter. Field: [Optional argument] This is the column number that you want to filter. This is counted from the left in the dataset.

VBA AutoFilter Multiple Criteria from Filter Drop-down Menu Listbox. We can also use the The code in the box below can be copy/pasted into the VB Editor. AutoFilter Field:=iCol, _ Criteria1:=Array("Product 4", "Product 5", "Product 6"), '​Begins With - use asterisk as wildcard character at end of string . An example showing how to build a filter from a range (converting range to an array) without loop and use the array to set the filter. This example uses two worksheets (Sheet1, Sheet2) - where Sheet1 is the main dataset being filtered.

Criteria1, Optional, Variant, The criteria (a string; for example, "101"). Followed by an Array detailing the filtering Array(Level, Date). Where Level is Copy. Worksheets("Sheet1").Range("A1").AutoFilter _ Field:=1, _ Criteria1:="Otis", Have questions or feedback about Office VBA or this documentation? AutoFilter in Excel VBA. It is very easy to apply the filter through just by pressing Alt + D + F + F simultaneously or Shift + Ctrl + L together. We can even go to the Data menu tab and select the Filter option there.

Comments
  • It works well, thank you :) Could you tell me what the ary is? It is not declared and it has no type and i don't understand it.
  • @user3683947 ary is a zero-based array of Type String ...............AutoFilter likes to use this for Criteria1
  • Is it possible to use this and create two user input variables to make this a universal macro, like this: