Filter range by multiple criteria

[_Khalil_]

Hi !

This is what I need to do:
I have a range (lista) that contains strings in column 2, and I want to filter it by those cells that contain any of the words in a string called myarray.
Being myarray for example {"one","two","three"}, i would need to filter lista by the cells that contain either "one" OR "two" OR "three".

This is what i have now but it filters data that contain only the first element of the array:

Sub FiltradoPrueba()
      Dim myarray As Variant
      myarray = Range("Conceptos").Value

      ActiveSheet.Range("LISTA").AutoFilter Field:=2, Criteria1:=myarray, Operator:=xlFilterValues

End Sub

I'd greatly appreciate any light on this.

Greetings from Uruguay.

Adrián

 

[pmich]

Try this:

G1 has exact heading of A1.
H1 has exact heading of B1.
H2 has *One*
H3 has *Two*
H4 has *Three*

Private Sub cmdAutoAdvFilter_MultipleCriteria_Click()
 Sheets("Sheet1").Select
 Range("B1").Select
 LastRecNum = Cells(Rows.Count, "B").End(xlUp).Row
 Dim LISTA As Range
 Set LISTA = ActiveSheet.Range("A1:B" & Trim(Str(LastRecNum)))
 rng.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range( _
   "G1:H4"), Unique:=False
End Sub

 

[_Khalil_]

It worked!! Thank u so much!!

I adapted your code to my spreadsheet this way:

Sub cmdAutoAdvFilter_MultipleCriteria_Click()
 Range("LISTA").Select
 Set Rng = ActiveSheet.Range("LISTA")
 Rng.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("Conceptos"), Unique:=False
End Sub

 

[Marcelo Branco]

Hi !

This is what I need to do:
I have a range (lista) that contains strings in column 2, and I want to filter it by those cells that contain any of the words in a string called myarray.
Being myarray for example {"one","two","three"}, i would need to filter lista by the cells that contain either "one" OR "two" OR "three".

This is what i have now but it filters data that contain only the first element of the array:

Sub FiltradoPrueba()
      Dim myarray As Variant
      myarray = Range("Conceptos").Value

      ActiveSheet.Range("LISTA").AutoFilter Field:=2, Criteria1:=myarray, Operator:=xlFilterValues

End Sub

I'd greatly appreciate any light on this.

Greetings from Uruguay.

Adrián

Try

Sub FiltradoPrueba()
      Dim myarray As Variant
      myarray = Application.Transpose(Range("Conceptos"))
      ActiveSheet.Range("LISTA").AutoFilter Field:=2, Criteria1:=myarray, Operator:=xlFilterValues
End Sub

M.

 

[pmich]

Thanks for the reply. Glad to know that it worked. Good that you have adapted it to your need.