Use Advanced Filter instead of AutoFilter. Here is an example adding 2 more excluded values.How do I write code to filter out more than two 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
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 ..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 ?
Rich (BB code):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
Just add 'blank' as one of the values to exclude like the othersOne last thing for this topic peter, what should I write if I want to filter out the blank cells ?
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.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 ?
Just add 'blank' as one of the values to exclude like the others
Rich (BB code):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 |