PDA

View Full Version : [SOLVED] AdvanceFilter- Multiple Criteria in one Cell



Imdabaum
09-24-2019, 05:03 PM
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
09-24-2019, 05:17 PM
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
09-24-2019, 06:39 PM
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
09-25-2019, 04:42 AM
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
10-01-2019, 06:01 PM
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
10-01-2019, 08:27 PM
Maybe it is time to make a simple example file and post it?

snb
10-02-2019, 03:42 AM
No, it's high time.

Imdabaum
10-11-2019, 10:04 AM
I stand corrected. Perhaps I was just mistyping the headers. It works with a second header column in the filter range.