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

Capture.JPG

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.