'Combine xlFilterValues with xlOR for other columns
I have a macro where I want to autofilter values. I have a list of values on one sheet which I want to filter.
If there are multiple values to be found (with or operator), they are delimited by comma in the cell. Here is the code that I run over multiple columns (this is an example for column D only)
If DL.Cells(i, "D").Value <> vbNullString Then
If InStr(1, DL.Cells(i, "D").Value, ",", vbTextCompare) > 0 Then
arr = Split(DL.Cells(i, "D").Value, ",", , vbTextCompare)
tbl.Range.AutoFilter Field:=35, Criteria1:=arr, Operator:=xlFilterValues
Else
tbl.Range.AutoFilter Field:=35, Criteria1:="=" & DL.Cells(i, "D").Value, Operator:=xlOr
End If
End If
I need to go over multiple columns and filter our either a single value or multiple values. And I want to have the relationship between columns to be xlor. So if I filter based on criteria written in five columns, if any of them is met, the record will be included.
The problem is that if my first column was filtered with tbl.Range.AutoFilter Field:=35, Criteria1:=arr, Operator:=xlFilterValues and the second column was filtered with tbl.Range.AutoFilter Field:=36, Criteria1:="=" & DL.Cells(i, "E").Value, Operator:=xlOr it does not take into account that I want to both filter multiple values in one column or have a single value in second column or multiple values in thrid column etc.
Thank you for any help.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
