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
*****
A-07	:	STZ1	(ALARM	BEGIN)
*****	DBG00
---------------
*****	STZ0
RICHIESTA	START	DOSATORE
*****	DBG00
---------------
[N][N]	STZ	1*	Set
---------------
[N][N]	STZ	1*	Set
---------------
[N][N]	STZ	1*	Set
---------------
[N][N]	STZ	1*	Set
---------------
[N][N]	STZ	3*	CD_U
---------------
[N][N]	STZ	1*	Set
---------------
[N][N]	STZ	1*	Set
---------------
[N][N]	STZ	3*	CD_U
--------	Ciclo	terminato
---------------
[N][N]	STZ	NORM	PS
---------------
[N][N]	STZ	NORM	PS
--------	Ciclo	terminato
---------------
[N][N]	STZ	NORM	PS
---------------
[N][N]	STZ	NORM	PS
--------	Ciclo	terminato
---------------	STZ2
[N][N]	STZ	NORM	PS
---------------
[N][N]	STZ	NORM	PS
--------	Ciclo	terminato
---------------	FROM
[N][N]	STZ	NORM	PS

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]1814[/TD]
[TD="align: right"]24/05/2018[/TD]
[TD="align: right"]12:52:15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1815[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1816[/TD]
[TD="align: right"]24/05/2018[/TD]
[TD="align: right"]12:52[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1817[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1818[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1819[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1820[/TD]
[TD="align: right"]24/05/2018[/TD]
[TD="align: right"]12:54:10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1821[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]1822[/TD]
[TD="align: right"]24/05/2018[/TD]
[TD="align: right"]12:53[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]1823[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]1824[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]1825[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]1826[/TD]
[TD="align: right"]24/05/2018[/TD]
[TD="align: right"]12:54:36[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]18520,00[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]1827[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]1828[/TD]
[TD="align: right"]24/05/2018[/TD]
[TD="align: right"]12:54:41[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]18520,00[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]1829[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]1830[/TD]
[TD="align: right"]24/05/2018[/TD]
[TD="align: right"]12:54:45[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]18520,00[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]1831[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]1832[/TD]
[TD="align: right"]24/05/2018[/TD]
[TD="align: right"]12:54:51[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]18520,00[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]1833[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]1834[/TD]
[TD="align: right"]24/05/2018[/TD]
[TD="align: right"]12:54:59[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]340,4[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]1835[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]1836[/TD]
[TD="align: right"]24/05/2018[/TD]
[TD="align: right"]12:55:03[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"]1480,00[/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]1837[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]1838[/TD]
[TD="align: right"]24/05/2018[/TD]
[TD="align: right"]12:55:07[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"]1480,00[/TD]

[TD="align: center"]26[/TD]
[TD="align: right"]1839[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="align: right"]1840[/TD]
[TD="align: right"]24/05/2018[/TD]
[TD="align: right"]12:55:13[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"]1150,6[/TD]

[TD="align: center"]28[/TD]
[TD="align: right"]1841[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]
[TD="align: right"]1842[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]
[TD="align: right"]1843[/TD]
[TD="align: right"]24/05/2018[/TD]
[TD="align: right"]12:55:46[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]-33,63[/TD]

[TD="align: center"]31[/TD]
[TD="align: right"]1844[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]32[/TD]
[TD="align: right"]1845[/TD]
[TD="align: right"]24/05/2018[/TD]
[TD="align: right"]12:55:52[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"]18997,31[/TD]

[TD="align: center"]33[/TD]
[TD="align: right"]1846[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]34[/TD]
[TD="align: right"]1847[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]35[/TD]
[TD="align: right"]1848[/TD]
[TD="align: right"]24/05/2018[/TD]
[TD="align: right"]12:56:25[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]-28,10[/TD]

[TD="align: center"]36[/TD]
[TD="align: right"]1849[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]37[/TD]
[TD="align: right"]1850[/TD]
[TD="align: right"]24/05/2018[/TD]
[TD="align: right"]12:56:31[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"]19017,79[/TD]

[TD="align: center"]38[/TD]
[TD="align: right"]1851[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]39[/TD]
[TD="align: right"]1852[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]40[/TD]
[TD="align: right"]1853[/TD]
[TD="align: right"]24/05/2018[/TD]
[TD="align: right"]12:57:04[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]-26,49[/TD]

[TD="align: center"]41[/TD]
[TD="align: right"]1854[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]42[/TD]
[TD="align: right"]1855[/TD]
[TD="align: right"]24/05/2018[/TD]
[TD="align: right"]12:57:10[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"]18680,38[/TD]

[TD="align: center"]43[/TD]
[TD="align: right"]1856[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]44[/TD]
[TD="align: right"]1857[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]45[/TD]
[TD="align: right"]1858[/TD]
[TD="align: right"]24/05/2018[/TD]
[TD="align: right"]12:57:42[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]-28,23[/TD]

</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