Using CriteriaRange to exclude blank cells in a Range with AdvancedFilter XLS2003

[LolaM]

OK guys, I am having trouble with the following...

In column A I have several cells containing data, which may have multiple entries of the same data. Along with this there are potentially blank cells.

I want to perform an AdvancedFilter on this column to take only the cells which have data, and copy a unique entry of each of these data elements to column B - in no particular order.

So for example if column A contains:
A1: Apple
A2: <blank>
A3: Orange
A4: <blank>
A5: Banana
A6: Apple
A7: Apple

Then running the AdvancedFilter would result in the following in column B
B1: Apple
B2: Orange
B3: Banana

ie. the blanks are removed and only one entry of identical entries in column A is copied to column B.

At the moment I can get the AdvancedFilter to copy the unique entries from col A to col B, but if <blank>cell A1 is blank, then cell B1 is <blank>blank, with the unique data in cells B2 downwards.

I *think* I should be looking at adding a CriteriaRange:= argument to the AdvancedFilter, but I'm struggling on how to write this to exclude blanks.

Any help you can offer would be greatly appreciated! Thanks in advance!</blank></blank></blank></blank>

 

[Andrew Poulsom]

Re: Using CriteriaRange to exclude blank cells in a Range with AdvancedFilter XLS2003 SOLVED

It's exactly as you see it, but there must be a header.

Excel 2010
	A	B	C	D	E	F	G
1	NumberPlate	Name	Km		NumberPlate
2	MIN-423	name 1	0		<>MMG-561
3	MDN-229	name 2	0
4	MDN-229	name 3	67.6		NumberPlate	Name	Km
5	MMG-561	name 4	0		MIN-423	name 1	0
6					MDN-229	name 2	0
7					MDN-229	name 3	67.6
Sheet1

 

[DocAElstein]

Re: Using CriteriaRange to exclude blank cells in a Range with AdvancedFilter XLS2003 SOLVED

Thanks again Andrew.
. I appreciate the patience
. I have continued to experiment. I now find I can get the <>MMG-561 criteria to work. But only if I have no empty cells in column A, as in your screen shot. Possibly I cannot have multiple criteria??)



. This syntax is certainly a tricky one. I just noticed for example that if I have no criteria, then in fact blank cells are sometimes ignored!!, ( So I appear not to need the solutions from post #2 or #3 always?!)



AND….

.. for the solution from post #7 I can type in exactly any of the following!!

???-
???-?
???-??
???-???
="???-"
="???-?"
="???-??"
="???-???"

But any of these would also allow an invalid License plate of the form for example MDN-22967867. So I do not appear to have a syntax to exactly tie down the format?

…. All very confusing!!!
Thanks again for all your help.
. I will keep at it, but must take a break now as it is driving me a bit crazy!!

Alan

 

[pgc01]

Re: Using CriteriaRange to exclude blank cells in a Range with AdvancedFilter XLS2003 SOLVED

Hi Alan

.. for the solution from post #7 I can type in exactly any of the following!!

???-
???-?
???-??
???-???
="???-"
="???-?"
="???-??"
="???-???"

But any of these would also allow an invalid License plate of the form for example MDN-22967867. So I do not appear to have a syntax to exactly tie down the format?

The criteria you are using only define the beginning of the strings, that's why the MDN-22967867 is accepted.

If you want a criterion for 7 characters (and only 7) in which the 4th is "-", 2 ways:

1 - with the cell formatted as General use:

="=???-???"

2 - First format the cell as text and then use:

=???-???

(which is the same value in the criterion cell as the one in option 1)

Please try.

 

[DocAElstein]

Re: Using CriteriaRange to exclude blank cells in a Range with AdvancedFilter XLS2003 SOLVED

…..
The criteria you are using only define the beginning of the strings, …
…If you want a criterion for 7 characters (and only 7) in which the 4th is "-", 2 ways:

1 - with the cell formatted as General use:

="=???-???"

2 - First format the cell as text and then use:

=???-???
….

. Thanks for that contribution PGC. (and the gem of info that despite hours of googling I could not find!! )
……
. That’s going some way to clearing things up and returning a small piece of my sanity – (Excel was probably somewhere along the lines of my experimenting “guessing” formats and throwing a Spanner in the works!) – your 2 suggested solutions Appear to work for me.
. I am still wondering if there is any known logic to these solutions or it is all based on guess work with experienced users like you “guessing” it mostly right!!
……………

………
Please try….

.. you asked for it!!!!

…… going back to something similar to my original examples from post #6, but for clarity and consistence with Andrew Poulsom using E column for criteria range.. and trying any of your 2 solutions gives ( Filtered column A results tacked on at end of main unfiltered table as before ): ( ….and in the screen shot I chose option Values so we are “seeing” here the text format from the formula which I typed in as formula
="=???-???"
……

……..
1 - with the cell formatted as General use:
="=???-???"
2 - First format the cell as text and then use:
=???-???
(which is the same value in the criterion cell as the one in option 1)
………..

…..and which Excel is evaluating out as the Text =???-??? (… that is making some sense to me now..…) )
…values screen shot 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]Km[/td][td][/td][td]NumberPlate[/td][/tr]

[tr][td]

2​

[/td][td]MIN-423[/td][td]name 1[/td][td]0,0 [/td][td][/td][td]=???-???[/td][/tr]

[tr][td]

3​

[/td][td]MDN-229[/td][td]name 2[/td][td]0,0 [/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td]MMG-561[/td][td]name 4[/td][td]0,0 [/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]MDN-229[/td][td]name 5[/td][td]0,0 [/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]MDN-22953[/td][td]name 6[/td][td]0,9 [/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]MDN-229[/td][td]name 7[/td][td]0,6 [/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]hdshg[/td][td]name 8[/td][td]0,7 [/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td]MDN-229[/td][td]name 9[/td][td]0,0 [/td][td][/td][td][/td][/tr]

[tr][td]

11​

[/td][td]MAE-745[/td][td]name 10[/td][td]1,0 [/td][td][/td][td][/td][/tr]

[tr][td]

12​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

13​

[/td][td]NumberPlate[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

14​

[/td][td]MIN-423[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

15​

[/td][td]MDN-229[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

16​

[/td][td]MMG-561[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

17​

[/td][td]MAE-745[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]unfiltered[/td][/tr][/table]

….. code again for clarity..

[color=darkgreen]'[/color]
[color=blue]Sub[/color] PoulsonPJC5()
 
[color=blue]Dim[/color] lr [color=blue]As[/color] [color=blue]Long[/color], rng [color=blue]As[/color] Range, c [color=blue]As[/color] Range, lc [color=blue]As[/color] [color=blue]Long[/color]
 
[color=blue]Dim[/color] wks1 [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wks1 = ThisWorkbook.Worksheets("unfiltered") [color=darkgreen]'set sheet name - Give abbreviation for "unfiltered" sheet in ThisWorkbook all Objects, Properties and Methods of Object Worksheet obtainable to view in the intellisense given after typing . Dot[/color]
wks1.Range("A13:A200").ClearContents
 
    [color=darkgreen]'Part 1) Optional Start Bit to Delete Sheets / Tabs------------[/color]
    Application.DisplayAlerts = [color=blue]False[/color] [color=darkgreen]'Prevents being asked everytime if you really want to delete the Workbook[/color]
    [color=blue]Dim[/color] ws [color=blue]As[/color] Worksheet [color=darkgreen]'Tempory worksheet name. ws now has Methods and Properties of Worksheets obtained with typing . dot after it![/color]
    [color=blue]For[/color] [color=blue]Each[/color] ws [color=blue]In[/color] ActiveWorkbook.Worksheets [color=darkgreen]'We could alternatively use something like ThisWorkbook, Workbooks("Itsname.xlxm")[/color]
        [color=blue]If[/color] ws.Name <> "ASheetToKeep" And ws.Name <> wks1.Name [color=blue]Then[/color]   [color=darkgreen]'Check that Worksheet name is not that of any that you want (Name property here returns name without .xlsm bit on end)[/color]
        ws.Delete
        [color=blue]Else[/color] [color=darkgreen]'Presumably then the worksheet name is That of the first sheet or any you wish to keep[/color]
        [color=darkgreen]' do nothing (Don't delete it!)[/color]
        [color=blue]End[/color] [color=blue]If[/color]
    [color=blue]Next[/color] ws
    Application.DisplayAlerts = [color=blue]True[/color] [color=darkgreen]'Turn it back on[/color]
    [color=darkgreen]'---End Bit to delete any Sheets / Tabs--------------------[/color]
 
    [color=darkgreen]'Part 2) Produce New sheets based on valid Number Plates[/color]
    lr = wks1.Cells(Rows.Count, "A").End(xlUp).Row
    lc = 3
    wks1.Range("A1:A" & lr).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wks1.Range("E1:E2"), CopyToRange:=wks1.Range("A" & lr + 2), Unique:=[color=blue]True[/color]
    [color=blue]Set[/color] rng = wks1.Range("A" & lr + 3, wks1.Cells(Rows.Count, 1).End(xlUp))
        [color=blue]For[/color] [color=blue]Each[/color] c [color=blue]In[/color] rng [color=darkgreen]' Each c is each range, that is to say each cell in the entire range rng[/color]
            Sheets.Add After:=Sheets(Sheets.Count)
            ActiveSheet.Name = c.Value
            wks1.Range("A1", wks1.Cells(lr, lc)).AutoFilter 1, c.Value
            wks1.Range("A1", wks1.Cells(lr, lc)).SpecialCells(xlCellTypeVisible).Copy ActiveSheet.Range("A1")
            wks1.AutoFilterMode = [color=blue]False[/color]
        [color=blue]Next[/color]
    [color=darkgreen]'wks1.Range("A" & lr + 2, wks1.Cells(Rows.Count, 1).End(xlUp)).ClearContents 'empty extra takt on Unique values in LookUpColumn[/color]
    [color=darkgreen]'End part to produce new sheets--------------------------[/color]
 
wks1.Activate [color=darkgreen]'Activate sheet 1 just to see it[/color]
 
[color=blue]End[/color] [color=blue]Sub[/color]

.. similarly with Andrews last suggestion for exclusion criteria ( noting it will only work if I remove the empty cell in Column A ):

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]Km[/td][td][/td][td]NumberPlate[/td][/tr]

[tr][td]

2​

[/td][td]MIN-423[/td][td]name 1[/td][td]0,0 [/td][td][/td][td]<>MMG-561[/td][/tr]

[tr][td]

3​

[/td][td]MDN-229[/td][td]name 2[/td][td]0,0 [/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td]MMG-561[/td][td]name 4[/td][td]0,0 [/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]Not Empty[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]MDN-229[/td][td]name 5[/td][td]0,0 [/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]MDN-22953[/td][td]name 6[/td][td]0,9 [/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]MDN-229[/td][td]name 7[/td][td]0,6 [/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]hdshg[/td][td]name 8[/td][td]0,7 [/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td]MDN-229[/td][td]name 9[/td][td]0,0 [/td][td][/td][td][/td][/tr]

[tr][td]

11​

[/td][td]MAE-745[/td][td]name 10[/td][td]1,0 [/td][td][/td][td][/td][/tr]

[tr][td]

12​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

13​

[/td][td]NumberPlate[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

14​

[/td][td]MIN-423[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

15​

[/td][td]MDN-229[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

16​

[/td][td]Not Empty[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

17​

[/td][td]MDN-22953[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

18​

[/td][td]hdshg[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

19​

[/td][td]MAE-745[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]unfiltered[/td][/tr][/table]


…… I am still experimenting with endless combinations of criteria ranges to achieve a combination of your “accepted” format criteria and Andrews exclusion criteria to get for example this:
Using Excel 2007
[Table="width:, class:grid"][tr][td]-[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

1​

[/td][td]NumberPlate[/td][td]Name[/td][td]Km[/td][td][/td][/tr]

[tr][td]

2​

[/td][td]MIN-423[/td][td]name 1[/td][td]0,0 [/td][td][/td][/tr]

[tr][td]

3​

[/td][td]MDN-229[/td][td]name 2[/td][td]0,0 [/td][td][/td][/tr]

[tr][td]

4​

[/td][td]MMG-561[/td][td]name 4[/td][td]0,0 [/td][td][/td][/tr]

[tr][td]

5​

[/td][td]Not Empty[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]MDN-229[/td][td]name 5[/td][td]0,0 [/td][td][/td][/tr]

[tr][td]

7​

[/td][td]MDN-22953[/td][td]name 6[/td][td]0,9 [/td][td][/td][/tr]

[tr][td]

8​

[/td][td]MDN-229[/td][td]name 7[/td][td]0,6 [/td][td][/td][/tr]

[tr][td]

9​

[/td][td]hdshg[/td][td]name 8[/td][td]0,7 [/td][td][/td][/tr]

[tr][td]

10​

[/td][td]MDN-229[/td][td]name 9[/td][td]0,0 [/td][td][/td][/tr]

[tr][td]

11​

[/td][td]MAE-745[/td][td]name 10[/td][td]1,0 [/td][td][/td][/tr]

[tr][td]

12​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

13​

[/td][td]NumberPlate[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

14​

[/td][td]MIN-423[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

15​

[/td][td]MDN-229[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

16​

[/td][td]MAE-745[/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]unfiltered[/td][/tr][/table]

… simply increasing the criteria range to CriteriaRange:=wks1.Range("E1:E3") with this

Using Excel 2007
[Table="width:, class:grid"][tr][td]-[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td]NumberPlate[/td][/tr]

[tr][td]

2​

[/td][td]<>MMG-561[/td][/tr]

[tr][td]

3​

[/td][td]=???-???[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]unfiltered[/td][/tr][/table]

Or

Using Excel 2007
[Table="width:, class:grid"][tr][td]-[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td]NumberPlate[/td][/tr]

[tr][td]

2​

[/td][td]=???-???[/td][/tr]

[tr][td]

3​

[/td][td]<>MMG-561[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]unfiltered[/td][/tr][/table]

does not appear to work. - It appears to give results as if CriteriaRange:=wks1.Range("E1:E2"), so ignoring the second criteria!!?!
………………..
. This is strange as my multiple criteria ( all “inclusion” criteria*** ) from code 1 ( CriteriaRange:=wks1.Range("C20:C24") ) appears to work for multiple criteria..
.. ( first as with my original format ="MDN-229" etc )

Using Excel 2007
[Table="width:, class:grid"][tr][td]-[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr]
[tr][td]

1​

[/td][td]NumberPlate[/td][td]Name[/td][td]Km[/td][/tr]

[tr][td]

2​

[/td][td]MIN-423[/td][td]name 1[/td][td]0,0 [/td][/tr]

[tr][td]

3​

[/td][td]MDN-229[/td][td]name 2[/td][td]0,0 [/td][/tr]

[tr][td]

4​

[/td][td]MMG-561[/td][td]name 4[/td][td]0,0 [/td][/tr]

[tr][td]

5​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]MDN-229[/td][td]name 5[/td][td]0,0 [/td][/tr]

[tr][td]

7​

[/td][td]MDN-22953[/td][td]name 6[/td][td]0,9 [/td][/tr]

[tr][td]

8​

[/td][td]MDN-229[/td][td]name 7[/td][td]0,6 [/td][/tr]

[tr][td]

9​

[/td][td]hdshg[/td][td]name 8[/td][td]0,7 [/td][/tr]

[tr][td]

10​

[/td][td]MDN-229[/td][td]name 9[/td][td]0,0 [/td][/tr]

[tr][td]

11​

[/td][td]MAE-745[/td][td]name 10[/td][td]1,0 [/td][/tr]

[tr][td]

12​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

13​

[/td][td]NumberPlate[/td][td][/td][td][/td][/tr]

[tr][td]

14​

[/td][td]MIN-423[/td][td][/td][td][/td][/tr]

[tr][td]

15​

[/td][td]MDN-229[/td][td][/td][td][/td][/tr]

[tr][td]

16​

[/td][td]MMG-561[/td][td][/td][td][/td][/tr]

[tr][td]

17​

[/td][td]MDN-22953[/td][td][/td][td][/td][/tr]

[tr][td]

18​

[/td][td]MAE-745[/td][td][/td][td][/td][/tr]

[tr][td]

19​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

20​

[/td][td][/td][td][/td][td]NumberPlate[/td][/tr]

[tr][td]

21​

[/td][td][/td][td][/td][td]MIN-423[/td][/tr]

[tr][td]

22​

[/td][td][/td][td][/td][td]MDN-229[/td][/tr]

[tr][td]

23​

[/td][td][/td][td][/td][td]MMG-561[/td][/tr]

[tr][td]

24​

[/td][td][/td][td][/td][td]MAE-745[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]unfiltered[/td][/tr][/table]

… or additionally with the improved format from PGC ( ="=MDN-229" etc )

Using Excel 2007
[Table="width:, class:grid"][tr][td]-[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr]
[tr][td]

1​

[/td][td]NumberPlate[/td][td]Name[/td][td]Km[/td][/tr]

[tr][td]

2​

[/td][td]MIN-423[/td][td]name 1[/td][td]0,0 [/td][/tr]

[tr][td]

3​

[/td][td]MDN-229[/td][td]name 2[/td][td]0,0 [/td][/tr]

[tr][td]

4​

[/td][td]MMG-561[/td][td]name 4[/td][td]0,0 [/td][/tr]

[tr][td]

5​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]MDN-229[/td][td]name 5[/td][td]0,0 [/td][/tr]

[tr][td]

7​

[/td][td]MDN-22953[/td][td]name 6[/td][td]0,9 [/td][/tr]

[tr][td]

8​

[/td][td]MDN-229[/td][td]name 7[/td][td]0,6 [/td][/tr]

[tr][td]

9​

[/td][td]hdshg[/td][td]name 8[/td][td]0,7 [/td][/tr]

[tr][td]

10​

[/td][td]MDN-229[/td][td]name 9[/td][td]0,0 [/td][/tr]

[tr][td]

11​

[/td][td]MAE-745[/td][td]name 10[/td][td]1,0 [/td][/tr]

[tr][td]

12​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

13​

[/td][td]NumberPlate[/td][td][/td][td][/td][/tr]

[tr][td]

14​

[/td][td]MIN-423[/td][td][/td][td][/td][/tr]

[tr][td]

15​

[/td][td]MDN-229[/td][td][/td][td][/td][/tr]

[tr][td]

16​

[/td][td]MMG-561[/td][td][/td][td][/td][/tr]

[tr][td]

17​

[/td][td]MAE-745[/td][td][/td][td][/td][/tr]

[tr][td]

18​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

19​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

20​

[/td][td][/td][td][/td][td]NumberPlate[/td][/tr]

[tr][td]

21​

[/td][td][/td][td][/td][td]=MIN-423[/td][/tr]

[tr][td]

22​

[/td][td][/td][td][/td][td]=MDN-229[/td][/tr]

[tr][td]

23​

[/td][td][/td][td][/td][td]=MMG-561[/td][/tr]

[tr][td]

24​

[/td][td][/td][td][/td][td]=MAE-745[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]unfiltered[/td][/tr][/table]

…

… the madness continues… maybe “inclusion” ( “accepted” )*** and “exclusion” criteria are not allowed together???

Alan

P.s. just a minor extra bit of madness noticed along the way….. a strange syntax anomaly:…
…….. ….this syntax is not allowed

wks1.Range("A1:A" & lr).AdvancedFilter Action:=xlFilterCopy, , CopyToRange:=wks1.Range("A" & lr + 2), Unique:=True

… but thess are….

wks1.Range("A1:A" & lr).AdvancedFilter xlFilterCopy, , CopyToRange:=wks1.Range("A" & lr + 2), Unique:=True
 
wks1.Range("A1:A" & lr).AdvancedFilter xlFilterCopy, , wks1.Range("A" & lr + 2), True

 

[DocAElstein]

Re: Using CriteriaRange to exclude blank cells in a Range with AdvancedFilter XLS2003 SOLVED

…. Just feeding back….

… my experiments so far appear to be telling me (XL 2007):

. 1a) I can have any amount of “inclusion” criteria.
. 1b) Careful consideration should be made to the different format for these “inclusion” , as pgc01 explained in post #13. ( Again to clarify: (for the case of standard cell format):
=”abc” allows entries such as abc abcd abc-339573285 etc .
=”=abc” allows only abc as an entry
…. ( for the case of text cell format )
abc allows entries such as abc abcd abc-339573285 etc .
=abc allows only abc as an entry


. 2) It appears that only one “exclusion” criteria is possible ( and once I have an exclusion I cannot have any “inclusion” criteria.
. 2b) Once the one “exclusion” criteria is chosen one cannot have empty cells ( which is what threw me off getting that option to work for a long time as I had an empty cell ) – again one cannot allow for that with any additional criteria as we are limited to one exclusion criteria, or rather once we choose the exclusion criteria option we are limited to just that one criteria. (Maybe there is some clever way to get over that??)

. 3a) A last important note I almost missed: Not using any criteria is dangerous: In my example, removing the criteria argument, it ignored the entry hdshg, so I guess Excel was guessing the format type!!! ( It excepted in this case something like hds-345 or hds-shg .
. A criteria such as ? or =”?” would appear to be necessary if entries are not all of similar types.
. 3b) Once any criteria is given empty cells would appear to be ignored (But I note here that sometimes without any criteria empty cells were ignored - maybe a case of Excel sometimes "guessing that an empty cell is an invalid format compared to the other format entries!!!)

. Alan

P.s.
( maybe someone in the know could Blog all this – or tell me where it is blogged already??)

 

[Andrew Poulsom]

Re: Using CriteriaRange to exclude blank cells in a Range with AdvancedFilter XLS2003 SOLVED

P.s. just a minor extra bit of madness noticed along the way….. a strange syntax anomaly:…
…….. ….this syntax is not allowed

wks1.Range("A1:A" & lr).AdvancedFilter Action:=xlFilterCopy, , CopyToRange:=wks1.Range("A" & lr + 2), Unique:=True

… but thess are….

wks1.Range("A1:A" & lr).AdvancedFilter xlFilterCopy, , CopyToRange:=wks1.Range("A" & lr + 2), Unique:=True
 
wks1.Range("A1:A" & lr).AdvancedFilter xlFilterCopy, , wks1.Range("A" & lr + 2), True

If you use named arguments there is no need for a placeholder for missing arguments.

Here's an article on Advanced Filter that yo may find helpful:

http://www.contextures.com/xladvfilter01.html

 

[DocAElstein]

Re: Using CriteriaRange to exclude blank cells in a Range with AdvancedFilter XLS2003 SOLVED

If you use named arguments there is no need for a placeholder for missing arguments.
………………

… Thanks for that Andrew. A coulple of follow up Questions here:
. It is a very minor point but I do not quite understand what You are saying here.

. 1) by “placeholder” are you referring to these “things” ?
Action:=
CriteriaRange:=
CopyToRange:=
Unique:=

. 2) what was actually troubling me was that if I omit these “things” ( As the convention allows ) I can choose to either have the criteria range argument like so

    wks1.Range("A1:A" & lr).AdvancedFilter  xlFilterCopy, wks1.Range("E1:E2"), wks1.Range("A" & lr + 2), [color=blue]True[/color]

Or not have it like so

    wks1.Range("A1:A" & lr).AdvancedFilter  xlFilterCopy,  , wks1.Range("A" & lr + 2), [color=blue]True[/color]

.. If on the other hand I do not admit these “things” ( which is a personal preference from me to understand and remember what the arguments are ) , then I am allowed to include all arguments so

    wks1.Range("A1:A" & lr).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wks1.Range("E1:E2"), CopyToRange:=wks1.Range("A" & lr + 2), Unique:=[color=blue]True[/color]

.. but I get a syntax error if now try to omit the CriteriaRange argument so

    wks1.Range("A1:A" & lr).AdvancedFilter Action:=xlFilterCopy,  , CopyToRange:=wks1.Range("A" & lr + 2), Unique:=[color=blue]True[/color]

. Please correct me if I am wrong, but I believe that in earlier versions of Excel it was required to include the “things”
Action:=
CriteriaRange:=
CopyToRange:=
Unique:=
.. Possibly the un allowed omission of the second (Criteria Range ) argument was a warning of the point .3a) I made in post #15 ?. That is to say it is wise to have a criteria range to be on the safe side, even if it is simply of the forms similar to
?
As discussed by us in this thread.

.. ( One very small but peculiar thing I then noticed was that I was allowed syntaxly to mix these two convention options ( the with and without the “things” option ) and could include at least the third and forth “things”
CopyToRange:=
Unique:=
along with omitting completely the second argument, provided I omitted the first “thing”
Action:=
… like so

    wks1.Range("A1:A" & lr).AdvancedFilter xlFilterCopy, , CopyToRange:=wks1.Range("A" & lr + 2), Unique:=[color=blue]True[/color]

… I agree this is a somewhat “extract” or “academic” observation. But possibly an interesting indication of how Excel is actually working.. – that is to say it goes into the “not thing” convention after I omit it in the first argument, which allows omitting completely the second argument, then at the third argument where I include the “thing” ( CopyToRange:= ) it goes into the “including thing” convention. )

………………

…….
Here's an article on Advanced Filter that you may find helpful:
….
………

. Thanks for that. There is a large amount of useful info there which I shall try to review in detail. At first glance I see no obvious reference to VBA to confirm my conclusions from post #15. But I should certainly try anyway to get up on the spreadsheet Advanced Filter. So thanks for the link. If you had the time I would be very grateful if could you comment on my conclusions from post #15, as I struggle still to google precise info and am still experimenting therefore. I lack your experience and only have time to do limited experiments.
. But in any case many thanks for your replies and info. Much appreciated.

Alan Elston

P.s. (Sorry if I have somewhat distracted (“Hijacked”) this thread – I just noticed that strangely at and after post #5 there is an extra SOLVED tacked on at the end of the title. Strange. – I thought at MrExcel threads were not possible to mark as solved. Indeed in the Excel Questions Forum List this extra SOLVED is not tacked on –another extract academic point I just noticed – sorry!!)

 

[Andrew Poulsom]

Re: Using CriteriaRange to exclude blank cells in a Range with AdvancedFilter XLS2003 SOLVED

I you use named arguments (e.g. Action:=) they can be in any order. If you omit them you have to provide the arguments in the order that the method expects, leaving placeholders for any that are omitted.

 

[DocAElstein]

Re: Using CriteriaRange to exclude blank cells in a Range with AdvancedFilter XLS2003 SOLVED

I you use named arguments (e.g. Action:=) they can be in any order. …...

Thanks. That lead me on to try this:

    wks1.Range("A1:A" & lr).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wks1.Range("A" & lr + 2), Unique:=True, CriteriaRange:=wks1.Range("E1:E2")

And then this:

    wks1.Range("A1:A" & lr).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wks1.Range("A" & lr + 2), Unique:=[color=blue]True[/color]

Giving me the possibility to include the three “named arguments”

Action:=
CopyToRange:=
Unique:=

While missing out the critical range

…
.. so the named arguments option does not like space holders, which makes sense I suppose if order is not important..

…..If you omit them you have to provide the arguments in the order that the method expects, leaving placeholders for any that are omitted.

.. so that clears up that point.
Thanks again..

Alan.

 

[DocAElstein]

Re: Using CriteriaRange to exclude blank cells in a Range with AdvancedFilter XLS2003 SOLVED

Hi,
. After many many hours of practice and experimenting I think I finally have 95% competence and understanding with the VBA Advanced filter using a Criteria range. I appear to be able to add a criteria range giving lots of different “allowed” formats and values ( including over multiple columns )..
.
. One very last problem is really bugging me. Before starting a new Thread to get help on this I thought I would tack the question on here….

. My last problem seems to be not being able to include more than one “not allowed” criteria. That is to say once I have one criteria to exclude an entry then I cannot then add any further criteria ( of any kind, “allowed” or “not allowed”.!!?)

. I have greatly simplified my example to demonstrate this problem:-

. Here is a listing of License Plates in Column A along with the Driver’s names in column B. My Criteria Range will be typed in Columns E to F . The Filtered Listing will be placed in Columns C to D.
. So here is the screen shot before running any code, with appropriate headings included for both the Copied Range and Criteria Range (In this case I am trying to exclude the License Plate MMG-561 )

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][td]

F​

[/td][/tr]
[tr][td]

1​

[/td][td]NumberPlate[/td][td]Name[/td][td]NumberPlate[/td][td]Name[/td][td]NumberPlate[/td][td]Name[/td][/tr]

[tr][td]

2​

[/td][td]MIN-423[/td][td]name 1[/td][td][/td][td][/td][td]<>MMG-561[/td][td][/td][/tr]

[tr][td]

3​

[/td][td]MDN-229[/td][td]name 2[/td][td][/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][td][/td][/tr]

[tr][td]

5​

[/td][td][/td][td][/td][td][/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][td][/td][/tr]

[tr][td]

7​

[/td][td]MDN-229[/td][td]name 6[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]hdshg[/td][td]name 8[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]MDN-229[/td][td]name 9[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td]MAE-745[/td][td]name 10[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]VBAAdvancedFilterCriteria[/td][/tr][/table]

. After running this code, I achieve the desired results, that is to say License Plate MMG-561 is excluded.

Code

[color=blue]Sub[/color] NumberplatesSimplified()
 
Range("A1:B10").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("C1:D10"), Unique:=False, CriteriaRange:=Range("E1:F2")
 
[color=blue]End[/color] [color=blue]Sub[/color]

Results:

Using Excel 2007
[Table="width:, class:grid"][tr][td]-[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

1​

[/td][td]NumberPlate[/td][td]Name[/td][td]NumberPlate[/td][td]Name[/td][/tr]

[tr][td]

2​

[/td][td]MIN-423[/td][td]name 1[/td][td]MIN-423[/td][td]name 1[/td][/tr]

[tr][td]

3​

[/td][td]MDN-229[/td][td]name 2[/td][td]MDN-229[/td][td]name 2[/td][/tr]

[tr][td]

4​

[/td][td]MMG-561[/td][td]name 4[/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td][/td][td][/td][td]MDN-229[/td][td]name 5[/td][/tr]

[tr][td]

6​

[/td][td]MDN-229[/td][td]name 5[/td][td]MDN-229[/td][td]name 6[/td][/tr]

[tr][td]

7​

[/td][td]MDN-229[/td][td]name 6[/td][td]hdshg[/td][td]name 8[/td][/tr]

[tr][td]

8​

[/td][td]hdshg[/td][td]name 8[/td][td]MDN-229[/td][td]name 9[/td][/tr]

[tr][td]

9​

[/td][td]MDN-229[/td][td]name 9[/td][td]MAE-745[/td][td]name 10[/td][/tr]

[tr][td]

10​

[/td][td]MAE-745[/td][td]name 10[/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]VBAAdvancedFilterCriteria[/td][/tr][/table]

…..

.. I cannot seem to find any combination of criteria that will allow me to exclude more than one license plate. Indeed, once I have one “not allowed” criteria I cannot add any more criteria of any kind.

. For example:
. If this is now my screen shot before running any 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][td]

F​

[/td][/tr]
[tr][td]

1​

[/td][td]NumberPlate[/td][td]Name[/td][td]NumberPlate[/td][td]Name[/td][td]NumberPlate[/td][td]Name[/td][/tr]

[tr][td]

2​

[/td][td]MIN-423[/td][td]name 1[/td][td][/td][td][/td][td]<>MMG-561[/td][td][/td][/tr]

[tr][td]

3​

[/td][td]MDN-229[/td][td]name 2[/td][td][/td][td][/td][td]<>MAE-745[/td][td][/td][/tr]

[tr][td]

4​

[/td][td]MMG-561[/td][td]name 4[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td][/td][td][/td][td][/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][td][/td][/tr]

[tr][td]

7​

[/td][td]MDN-229[/td][td]name 6[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]hdshg[/td][td]name 8[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]MDN-229[/td][td]name 9[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td]MAE-745[/td][td]name 10[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]VBAAdvancedFilterCriteria[/td][/tr][/table]

.. This is one code example of many that I was hoping would exclude the two license plates MMG-561 and MAE-745 ( I have simply in this case taken the last code and increased the criteria range to include the extra license Plate )

[color=blue]Sub[/color] NumberplatesSimplified()
 
Range("A1:B10").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("C1:D10"), Unique:=False, CriteriaRange:=Range("E1:F3")
 
[color=blue]End[/color] [color=blue]Sub[/color]

… The following results are obtained after running the code. I get similar results in many many experiments involving trying to include any other criteria once I have one “not allowed” criterion

Using Excel 2007
[Table="width:, class:grid"][tr][td]-[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

1​

[/td][td]NumberPlate[/td][td]Name[/td][td]NumberPlate[/td][td]Name[/td][/tr]

[tr][td]

2​

[/td][td]MIN-423[/td][td]name 1[/td][td]MIN-423[/td][td]name 1[/td][/tr]

[tr][td]

3​

[/td][td]MDN-229[/td][td]name 2[/td][td]MDN-229[/td][td]name 2[/td][/tr]

[tr][td]

4​

[/td][td]MMG-561[/td][td]name 4[/td][td]MMG-561[/td][td]name 4[/td][/tr]

[tr][td]

5​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]MDN-229[/td][td]name 5[/td][td]MDN-229[/td][td]name 5[/td][/tr]

[tr][td]

7​

[/td][td]MDN-229[/td][td]name 6[/td][td]MDN-229[/td][td]name 6[/td][/tr]

[tr][td]

8​

[/td][td]hdshg[/td][td]name 8[/td][td]hdshg[/td][td]name 8[/td][/tr]

[tr][td]

9​

[/td][td]MDN-229[/td][td]name 9[/td][td]MDN-229[/td][td]name 9[/td][/tr]

[tr][td]

10​

[/td][td]MAE-745[/td][td]name 10[/td][td]MAE-745[/td][td]name 10[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]VBAAdvancedFilterCriteria[/td][/tr][/table]

… can anyone help?
. Have I hit a fundamental limitation . - That being that I may only use one “not allowed” criterion ?

Thanks
Alan

P.s. I have checked codes and files in ( PC ) XL 2007 2010 2003. Results are identical