PDA

View Full Version : [SOLVED] Filter more than 2 values



Hector
08-21-2015, 04:57 PM
Hello guys.

In the code below, I am getting data (comma separated) from Range AO21, which would be the criteria to search for in a filter.
Lets say, Range AO21 value is "APPLES, GRAPES, ORANGES".

The code below will only search for apples and grapes, which are the first 2 words comma separated in range AO21. I need a code to let me have several comma separated values to filter, no matter how many.


Dim FRUITS As String
Dim Data As Variant
INVOICE_WORKBOOK.Activate
Range("AO21").Select
FRUITS = ActiveCell.Value
PIVOT_WORKBOOK.Activate
If InStr(1, FRUITS, ",") > 0 Then
Data = Split(FRUITS, ",")
Range("A1").AutoFilter Field:=1, Criteria1:=Trim(Data(0)), Operator:=xlOr, Criteria2:=Trim(Data(1))
Else
Range("A1").AutoFilter Field:=1, Criteria1:=FRUITS
End If

if anybody could take a minute to help me, I would be really appreciated.

Hector

mancubus
08-22-2015, 08:03 AM
welcome to vbax hector.

please use code tags when pasting your code here.
you can either
type in the tags, like [ CODE ]Paste your code here[ /CODE ] (remove spaces after and before brackets, [])
or
click # button after pasting and selecting your code.


that said, i prefer AdvanceFilter method for filtering with more than 2 criteria.

it seems you are familiar with VBA, so you can analyze the code below.

change Sheet1's to actual worksheet names in the workbooks. i assume INVOICE_WORKBOOK and PIVOT_WORKBOOK are workbook variables.



Sub vbax_53546_FilterColumnMoreThan2Criteria()

Dim CritArr

With INVOICE_WORKBOOK.Worksheets("Sheet1")
CritArr = Split(Replace(.Range("AO21").Value, " ", ""), ",")
End With

With PIVOT_WORKBOOK.Worksheets("Sheet1")
.Cells(1, .Columns.Count).Value = .Range("A1").Value
.Cells(2, .Columns.Count).Resize(UBound(CritArr) + 1).Value = Application.Transpose(CritArr)
.Cells(1).CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=.Cells(1, .Columns.Count).CurrentRegion
.Cells(1, .Columns.Count).CurrentRegion.Clear
End With

End Sub

Hector
08-22-2015, 04:30 PM
Thank you so much Mancubus!