Re: Using CriteriaRange to exclude blank cells in a Range with AdvancedFilter XLS2003 SOLVED
You don't need to have a copy of the column. You can use the same header twice in the criteria range:
…………
. Thanks for that. I was seeing from my practicing that juggling around with the Headings, ( their positions etc. ) was possible and appeared to be a key useful characteristic of the Advanced Filter Method. I had not hit on to that idea of duplicating a criteria range heading as a means of giving a criteria row to get multiple “entry exclusion” criteria. So thanks for that Tip
--- so using your first screen shot from post #22 and the following code I achieve my desired results
Code:
Code:
[color=blue]Sub[/color] NumberplatesPoulsomMultiExclusion_DiplicateCriteriaHeading()
Range("A1:B9").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("C1:D9"), Unique:=False, CriteriaRange:=Range("E1:F2")
[color=blue]End[/color] [color=blue]Sub[/color]
Thanks..
………………………………………………………….
………
If you want to use a formula you need to clear the header from the criteria range:
Excel 2010
[TABLE="width: 0"]
<thead>[TR]
[TH][/TH]
[TH]A
[/TH]
[TH]B
[/TH]
[TH]C
[/TH]
[TH]D
[/TH]
[TH]E
[/TH]
[/TR]
</thead><tbody>[TR]
[TD]1
[/TD]
[TD]NumberPlate
[/TD]
[TD]Name
[/TD]
[TD]NumberPlate
[/TD]
[TD]Name
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]MIN-423
[/TD]
[TD]name 1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]TRUE
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]MDN-229
[/TD]
[TD]name 2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]MMG-561
[/TD]
[TD]name 4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]MDN-229
[/TD]
[TD]name 5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]MDN-229
[/TD]
[TD]name 5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]hdshg
[/TD]
[TD]name 8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]MDN-229
[/TD]
[TD]name 9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]MAE-745
[/TD]
[TD]name 10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Sheet2
[TABLE="width: 919"]
<tbody>[TR]
[TD]Worksheet Formulas
[TABLE="width: 907"]
<thead>[TR]
[TH]Cell
[/TH]
[TH]Formula
[/TH]
[/TR]
</thead><tbody>[TR]
[TD]E2
[/TD]
[TD]=AND(A2<>"MMG-561",A2<>"MAE-745")
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
… Thanks for that. I was just getting into this idea. Again it was not in the contextures link, but by experimenting with various syntax in the criteria “FALSE And “TRUE” sometimes “popped up” in the cell!!. Following up from reviewing that link you gave did I hit on another similar contextures video:
https://www.youtube.com/watch?v=3hnNN0vciBQ
where something very similar is done. I was a little confused with the strange syntax need to remove ( or change ) the Heading, so it was very helpful that you applied / gave an exact solution to my requirement. Very helpful in getting the idea clear in my head!! (
I see now the logic to that requirement: I could include in the Boolean Bit other columns, such as in my example below. (Still a bit confusing that the syntax takes A2 and B2 rather than A? and C? as the criteria will be checked down for every column in the List Range. ).
. So for completeness a “Boolean Bit” example:
Before running any code:
<b>Excel 2007</b><table size=1 width="1" cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">NumberPlate</td><td style=";">Name</td><td style=";">NumberPlate</td><td style=";">Name</td><td style=";">JumbledCriteriaReferrencing</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">MIN-423</td><td style=";">name 1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">WAHR</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">MDN-229</td><td style=";">name 2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">MMG-561</td><td style=";">name 4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">MDN-229</td><td style=";">name 5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">MDN-229</td><td style=";">name 5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">hdshg</td><td style=";">name 8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">MDN-229</td><td style=";">name 9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="color: #333333;;">MAE-745</td><td style="color: #333333;;">name 10</td><td style="text-align: right;color: #333333;;"></td><td style="text-align: right;color: #333333;;"></td><td style="text-align: right;color: #333333;;"></td></tr></tbody></table><p style="width:15em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">VBAAdvancedFilterCriteria</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E2</th><td style="text-align:left">=AND(<font color="Blue">A2<>"MMG-561",A2<>"MAE-745",B2<>"name 8"</font>)</td></tr></tbody></table></td></tr></table><br />
And after running code:
Using Excel 2007
[Table="width:, class:grid"][tr][td]-[/td][td]A
[/td][td]B
[/td][td]C
[/td][td]D
[/td][td]E
[/td][/tr]
[tr][td]1
[/td][td]NumberPlate[/td][td]Name[/td][td]NumberPlate[/td][td]Name[/td][td]JumbledCriteriaReferrencing[/td][/tr]
[tr][td]2
[/td][td]MIN-423[/td][td]name 1[/td][td]MIN-423[/td][td]name 1[/td][td]=AND(A2<>"MMG-561",A2<>"MAE-745",B2<>"name 8")
[/td][/tr]
[tr][td]3
[/td][td]MDN-229[/td][td]name 2[/td][td]MDN-229[/td][td]name 2[/td][td]
[/td][/tr]
[tr][td]4
[/td][td]MMG-561[/td][td]name 4[/td][td]MDN-229[/td][td]name 5[/td][td]
[/td][/tr]
[tr][td]5
[/td][td]MDN-229[/td][td]name 5[/td][td]MDN-229[/td][td]name 5[/td][td]
[/td][/tr]
[tr][td]6
[/td][td]MDN-229[/td][td]name 5[/td][td]MDN-229[/td][td]name 9[/td][td]
[/td][/tr]
[tr][td]7
[/td][td]hdshg[/td][td]name 8[/td][td][/td][td][/td][td]
[/td][/tr]
[tr][td]8
[/td][td]MDN-229[/td][td]name 9[/td][td]
[/td][td]
[/td][td]
[/td][/tr]
[tr][td]9
[/td][td]MAE-745[/td][td]name 10[/td][td]
[/td][td]
[/td][td]
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]
VBAAdvancedFilterCriteria[/td][/tr][/table]
..
Code:
Code:
[color=blue]Sub[/color] NumberplatesPoulsomMultiExclusion_BooleanFormula()
Range("A1:B9").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("C1:D9"), Unique:=False, CriteriaRange:=Range("E1:E2")
[color=blue]End[/color] [color=blue]Sub[/color]
Thanks again Andrew for your generous help. Very grateful.
Alan Elston.
P.s.
. Thanks again for that contextures link. In fact the spreadsheet Advance Filter info there is very useful as it mirrors very closely the VBA Advanced Filter Method, (the info from pgc Post # 13 for example is also there and I had missed that elsewhere )
Indeed, from contextures.com one similar video ( not in that link so for anyone looking into this thread I give it here) …
https://www.youtube.com/watch?v=S0pXfzUW6C8
… this actually uses the macro recorder to produce a code of the form similar to those with which I have been experimenting with. It is very useful when someone with experience recommends a good source of info, as otherwise it can be very overwhelming the massive amount of info out there these days.
- I really most sort my brain out…
