AdvanceFilter- Multiple Criteria in one Cell

[Imdabaum]

Hello readers,

I have been working with a company spreadsheet that uses AdvanceFilter method on a range.
Everything works as expected, but I am not that familiar with how VBA interprets the criteria string.

Range("Data_Rng").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("Criteria"), CopyToRange:=Range("BA15:CR15"), Unique:=False

I need to be able to, if possible, enter multiple parameters in one cell to apply multiple filters at once.
For example

ACOLUMNFilter
============
<>3 AND <> 8


ACOLUMNDATA
===========
(All rows filtered that don't have 3 or 8 in this column for the dataset)

[Kenneth Hobs]

Criteria on same row, is And. On multiple rows is multiples Ors. To do the And for one column, duplicate the heading for Criteria. e.g.

e.g.


colD colD
<>3 <>8

[Paul_Hossler]

I always have trouble with the AF so I made a little example to help getting the criteria right. This is a cleaned up version

Probably a more elegant way to do it

25155



Option Explicit


Sub Macro1()


Dim rData As Range, rCrit As Range, rDest As Range

Set rData = Range("A1").CurrentRegion
Set rCrit = Range("G1").CurrentRegion
Set rDest = Range("K1")


rDest.CurrentRegion.ClearContents


Application.CutCopyMode = False
rData.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rCrit, CopyToRange:=rDest, Unique:=False
End Sub

[mana]

I need to be able to, if possible, enter multiple parameters in one cell to apply multiple filters at once.

You can use formula as criteria.


=and(A2<>3,A2<>8)

[Imdabaum]

I tried adding a second column with same heading and both filters stopped working.
This is the code the previous colleague had developed.


CritRows = Range("A1").CurrentRegion.Rows.Count
If CritRows = 1 Then
ActiveWorkbook.Names.Add Name:="Criteria", RefersTo:=Range("A1:AR2")
Else
Range("A1").CurrentRegion.Select
ActiveWorkbook.Names.Add Name:="Criteria", RefersTo:=Selection
End If

Range("A15").CurrentRegion.Select
ActiveWorkbook.Names.Add Name:="Data_Rng", RefersTo:=Selection

Range("Data_Rng").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("Criteria"), CopyToRange:=Range("BA15:CR15"), Unique:=False

Range("A15").Select


mana

You can use formula as..

=and(A2<>3,A2<>8)
That had no affect either.

[Kenneth Hobs]

Maybe it is time to make a simple example file and post it?

[snb]

No, it's high time.

[Imdabaum]

I stand corrected. Perhaps I was just mistyping the headers. It works with a second header column in the filter range.