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

Question

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.

Gary's Student · Accepted Answer · 2017-11-28 22:50:29Z

6

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

edited Nov 28, 2017 at 22:50

answered Mar 19, 2015 at 13:43

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3683947 Over a year ago

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.

Gary's Student Over a year ago

@user3683947 ary is a zero-based array of Type String ...............AutoFilter likes to use this for Criteria1

gma · Accepted Answer · 2016-09-19 07:05:19Z

2

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

edited Sep 19, 2016 at 7:05

answered Sep 19, 2016 at 6:22

gma

212 bronze badges

1 Comment

cspearsall Over a year ago

Is it possible to use this and create two user input variables to make this a universal macro, like this:

cspearsall · Accepted Answer · 2018-10-08 21:50:15Z

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

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related