Filter out multiple criteria

[kamalm]

Hi
I used these codes to filter out two values. How do I write code to filter out more than two values ?

Range("$A$1:$Z$100000").AutoFilter Field:=3, Criteria1:="<>Ciclo", Criteria2:="<>FROM", Operator:=xlAnd

 

[mrshl9898]

There doesn't look to be a direct way of doing this with AutoFilter, you can filter on more than 2 with an Array, but not <> for more than 2.

A couple of good ideas here: https://stackoverflow.com/questions/28575754/filter-out-multiple-criteria-using-excel-vba

 

[Peter_SSs]

How do I write code to filter out more than two values ?

Use Advanced Filter instead of AutoFilter. Here is an example adding 2 more excluded values.

Sub AdvFltr()
  Dim rCrit As Range
  
  Set rCrit = Range("AA1:AA2")
  rCrit.Cells(2).Formula = "=AND(C2<>{""Ciclo"",""FROM"",""TO"", ""abc""})"
  Range("$A$1:$Z$100000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rCrit, Unique:=False
End Sub

 

[kamalm]

Thanks Peter. But I'm a bit confused with the code. Where is the part that we set which column we want to use the Advanced Filter ?

 

[Peter_SSs]

Thanks Peter. But I'm a bit confused with the code. Where is the part that we set which column we want to use the Advanced Filter ?

It is in the formula in rCrit. In your first post you had a range starting in column A and were filtering on Field:=3 which would be column C. So in my formula ..

Sub AdvFltr()
  Dim rCrit As Range
  
  Set rCrit = Range("AA1:AA2")
  rCrit.Cells(2).Formula = "=AND(C2<>{""Ciclo"",""FROM"",""TO"", ""abc""})"
  Range("$A$1:$Z$100000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rCrit, Unique:=False
End Sub

 

[kamalm]

Now I get it and your code is work very well One last thing for this topic peter, what should I write if I want to filter out the blank cells ?

And, the code you gave me just now for one column. How the code looks like if I want to have multiple column with multiple values ?

 

[Peter_SSs]

One last thing for this topic peter, what should I write if I want to filter out the blank cells ?

Just add 'blank' as one of the values to exclude like the others

rCrit.Cells(2).Formula = "=AND(C2<>{""Ciclo"",""FROM"",""TO"", ""abc"",""""})"

And, the code you gave me just now for one column. How the code looks like if I want to have multiple column with multiple values ?

That is way too vague a description. Could you give a small set of sample dummy data, expected results and explanation in relation to that sample data? My signature block below has help, if needed, with how to show sample data that we can copy and test with.

 

[My Aswer Is This]

When you say:

I used these codes to filter out two values​

Can we just delete the rows that meet your criteria.
Filter out like your using does not delete the rows

 

[kamalm]

Just add 'blank' as one of the values to exclude like the others

rCrit.Cells(2).Formula = "=AND(C2<>{""Ciclo"",""FROM"",""TO"", ""abc"",""""})"

That is way too vague a description. Could you give a small set of sample dummy data, expected results and explanation in relation to that sample data? My signature block below has help, if needed, with how to show sample data that we can copy and test with.

	A	B	C	D	E	F	G	H	I
1	1814	24/05/2018	12:52:15
2	1815	*****
3	1816	24/05/2018	12:52
4	1817	A-07	:	STZ1	(ALARM	BEGIN)
5	1818	*****	DBG00
6	1819	---------------
7	1820	24/05/2018	12:54:10
8	1821	*****		STZ0
9	1822	24/05/2018	12:53
10	1823	RICHIESTA	START	DOSATORE
11	1824	*****	DBG00
12	1825	---------------
13	1826	24/05/2018	12:54:36	[N][N]	STZ	1	1*	Set	18520,00
14	1827	---------------
15	1828	24/05/2018	12:54:41	[N][N]	STZ	1	1*	Set	18520,00
16	1829	---------------
17	1830	24/05/2018	12:54:45	[N][N]	STZ	1	1*	Set	18520,00
18	1831	---------------
19	1832	24/05/2018	12:54:51	[N][N]	STZ	1	1*	Set	18520,00
20	1833	---------------
21	1834	24/05/2018	12:54:59	[N][N]	STZ	1	3*	CD_U	340,4
22	1835	---------------
23	1836	24/05/2018	12:55:03	[N][N]	STZ	3	1*	Set	1480,00
24	1837	---------------
25	1838	24/05/2018	12:55:07	[N][N]	STZ	3	1*	Set	1480,00
26	1839	---------------
27	1840	24/05/2018	12:55:13	[N][N]	STZ	3	3*	CD_U	1150,6
28	1841	--------	Ciclo	0	terminato
29	1842	---------------
30	1843	24/05/2018	12:55:46	[N][N]	STZ	1	NORM	PS	-33,63
31	1844	---------------
32	1845	24/05/2018	12:55:52	[N][N]	STZ	3	NORM	PS	18997,31
33	1846	--------	Ciclo	1	terminato
34	1847	---------------
35	1848	24/05/2018	12:56:25	[N][N]	STZ	1	NORM	PS	-28,10
36	1849	---------------
37	1850	24/05/2018	12:56:31	[N][N]	STZ	3	NORM	PS	19017,79
38	1851	--------	Ciclo	2	terminato
39	1852	---------------		STZ2
40	1853	24/05/2018	12:57:04	[N][N]	STZ	1	NORM	PS	-26,49
41	1854	---------------
42	1855	24/05/2018	12:57:10	[N][N]	STZ	3	NORM	PS	18680,38
43	1856	--------	Ciclo	3	terminato
44	1857	---------------	FROM
45	1858	24/05/2018	12:57:42	[N][N]	STZ	1	NORM	PS	-28,23

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Above is the sample data. I want o filter out:
Column C = Ciclo, DBG00, FROM, 'blank'
Column D = STZ0, STZ1, STZ2, DOSATORE, 'blank'
Column E = ALARM
Column H = Set

 

[My Aswer Is This]

Your original post seemed to indicate you wanted to filter out all values that do not equal:

Ciclo, DBG00, FROM​

But it seems to me your last post said filter out.
Values that do equal.

Would you clarify this