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

 

[kamalm]

I'm sorry if I have made you confused. I actually want to filter out those values that I mentioned above.

 

[Peter_SSs]

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

So, if we did all that with that sample data, am I right in thinking that the only remaining visible rows would be the ones shown green below? I have marked all the cells that meet any of your conditions with yellow so those rows would be filtered out as I understand it.

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

If I happen to be right about the remaining rows, then for this sample data the same result could be achieved with AutoFilter by filtering column D to only show [N][N] and column H to not show Set
Would that be true for any of your data?

 

[kamalm]

If I happen to be right about the remaining rows, then for this sample data the same result could be achieved with AutoFilter by filtering column D to only show [N][N] and column H to not show Set
Would that be true for any of your data?

omg you're so genius Peter. This way more simple and easy. Never realized that I actually want the rows that have [N][N] only. God bless you, thank you!

 

[Peter_SSs]

omg you're so genius Peter. This way more simple and easy. Never realized that I actually want the rows that have [N][N] only. God bless you, thank you!

Best not to fully quote long posts as it makes the thread harder to read/navigate. If you want to quote, quote small, relevant parts only. I've edited your post to do that.

Just to confirm, you need to AutoFilter two columns as there are some [N][N] rows that you apparently don't want. Is that what you are doing?

 

[kamalm]

Just to confirm, you need to AutoFilter two columns as there are some [N][N] rows that you apparently don't want. Is that what you are doing?

Yup. This is my code I did just now and it works.

Cells.Select
    Selection.AutoFilter
    ActiveSheet.Range("A1:A" & Range("B" & Rows.Count).End(xlUp).Row).AutoFilter Field:=4, Criteria1:="[N][N]"
    ActiveSheet.Range("A1:A" & Range("B" & Rows.Count).End(xlUp).Row).AutoFilter Field:=8, Criteria1:="<>Set"

Perhaps you have better or shorter one.

 

[Peter_SSs]

Perhaps you have better or shorter one.

I would definitely not 'Select' anything. Selecting is rarely needed and tends to slow your code and can make any screen flickering worse.
You also only need to calculate the range once.
This is how I would write it. (I've assumed the sheet's used range)

With ActiveSheet.UsedRange
  .AutoFilter Field:=4, Criteria1:="[N][N]"
  .AutoFilter Field:=8, Criteria1:="<>Set"
End With

 

[kamalm]

What is actually UsedRange ? Does it mean select all cells contain values ?

 

[Peter_SSs]

What is actually UsedRange ? Does it mean select all cells contain values ?

It can mean that but it can also get a bit tricky depending on what has been going on in your worksheet. You might have to do some research about that. Here's one place to start.

 

[kamalm]

It can mean that but it can also get a bit tricky depending on what has been going on in your worksheet. You might have to do some research about that. Here's one place to start.

Oh I see. Will go through it later. Thank you so much Peter. learnt a lot from you