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

LolaM

New Member
Joined
Sep 7, 2011
Messages
24
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
First you need a heading above your list in A1 an the same heading in B1. Let's say that's Fruit. Copy that heading to C1. In C2 enter:

="<>"

Then Advanced Filter unique records to B1 using the criteria C1:C2.
 

LolaM

New Member
Joined
Sep 7, 2011
Messages
24
Re: Using CriteriaRange to exclude blank cells in a Range with AdvancedFilter XLS2003 SOLVED

Thanks guys - worked perfectly!
 

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
Re: Using CriteriaRange to exclude blank cells in a Range with AdvancedFilter XLS2003 SOLVED

Hi,

. This Thread and others has helped me understand the use of the second argument (CriteriaRange:= ) in .AdvancedFilter, and answered a question of mine in Post #7 here
http://www.mrexcel.com/forum/excel-questions/850957-macro-split-1-worksheet-multiple-excel-files-name-cell-column.html
. Thanks!

. I can now use successfully .AdvancedFilter with second argumnent ( CriteriaRange:= ) for specifying valid values in a Look Up Column ( and also for ignoring blank cells in the Look Up Column ).
.
. I am having some last trouble in getting the syntax correct to Filter out invalid values or Formats in the Look Up Column. I am not sure if this is possible??
. Can anyone help?
.
. To demonstrate my problem I have made a simplified File with an unfiltered sheet containing truck Number Plates (License Plates) in column A. Also in this sheet I have included 4 Criteria ranges in an abritrary ranges below the main table ( In column C ), which correspond to the 4 codes given below.
. So The Unfiltered sheet looks like this before running any code:

Using Excel 2007
-
A
B
C
1
NumberPlateNameKm
2
MIN-423name 10,0
3
MDN-229name 20,0
4
MDN-229name 367,6
5
MMG-561name 40,0
6
7
MDN-229name 50,0
8
MDN-229name 60,9
9
MDN-229name 70,6
10
hdskjhname 80,7
11
MDN-229name 90,0
12
MAE-745name 101,0
13
14
15
16
17
18
19
20
NumberPlate
21
="MIN-423"
22
="MDN-229"
23
="MMG-561"
24
="MAE-745"
25
26
27
NumberPlate
28
?
29
30
NumberPlate
31
="<>"
32
33
34
NumberPlate
35
?
36
="<>"&"hdskjh"
unfiltered



After running, for example, the First code, I get this for the unfiltered sheet (The difference is just a temporary small unique table which would usually in the practice be deleted at the end of the code):

Using Excel 2007
-
A
B
C
1
NumberPlateNameKm
2
MIN-423name 10,0
3
MDN-229name 20,0
4
MDN-229name 367,6
5
MMG-561name 40,0
6
7
MDN-229name 50,0
8
MDN-229name 60,9
9
MDN-229name 70,6
10
hdskjhname 80,7
11
MDN-229name 90,0
12
MAE-745name 101,0
13
14
NumberPlate
15
MIN-423
16
MDN-229
17
MMG-561
18
MAE-745
19
20
NumberPlate
21
="MIN-423"
22
="MDN-229"
23
="MMG-561"
24
="MAE-745"
25
26
27
NumberPlate
28
?
29
30
NumberPlate
31
="<>"
32
33
34
NumberPlate
35
?
36
="<>"&"hdskjh"
unfiltered


. and in addition 4 new sheets are created. For example the second newly created sheet looks like this:

Using Excel 2007
-
A
B
C
1
NumberPlateNameKm
2
MDN-229name 20,0
3
MDN-229name 367,6
4
MDN-229name 50,0
5
MDN-229name 60,9
6
MDN-229name 70,6
7
MDN-229name 90,0
MDN-229
…..

. Similarly codes 2 and 3 give me expected results (Filters out empty cells). Code 4 does not work.
. So I am looking for suggestions for the 4th code and 4th criteria Range ( C34:C36 ) which will either Filter out specific invalid License numbers, ( such as the hdshg ) , or better still a syntax which filters out anything without the correct looking Format type.

. Thanks in advance
Alan Elston.
……


Codes: (Parts .2) are relevant bits for this problem)

Code:
[color=darkgreen]'[/color]
'
[color=blue]Sub[/color] makeTruckRecord1()
[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]
 
    [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("C20:C24"), 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 that sheet 1 just to see it[/color]
 
[color=blue]End[/color] [color=blue]Sub[/color]
[color=darkgreen]'[/color]
'
[color=blue]Sub[/color] makeTruckRecord2()
[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]
 
    [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("C27:C28"), 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 that sheet 1 just to see it[/color]
 
[color=blue]End[/color] [color=blue]Sub[/color]
[color=darkgreen]'[/color]
'
[color=blue]Sub[/color] makeTruckRecord3()
[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]
 
    [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("C30:C31"), 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]
[color=darkgreen]'[/color]
'
[color=blue]Sub[/color] makeTruckRecord4()
[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]
 
    [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("C34:C35"), 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]
[color=darkgreen]'[/color]
'
'
P.s. the example worksheet in case it helps. (XL 2007 WhizPolyShortCriteriaProbAdvasncedFilter.xlsm Code in Module WhizPolyn4ForumShortExample )
https://app.box.com/s/dvp86p724yifo9l60l4lryqpafi7yx4r
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Re: Using CriteriaRange to exclude blank cells in a Range with AdvancedFilter XLS2003 SOLVED

Can't you use the criteria?

???-???
 

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
Re: Using CriteriaRange to exclude blank cells in a Range with AdvancedFilter XLS2003 SOLVED

Can't you use the criteria?

???-???
Thanks Andrew.
. That works for getting / allowing the valid Format ( I guess I was somewhat confused with the “wildcard formats etc, ” and must read up again on those – I thought I had tried that along the way. :oops:)

. One last question- what would be the format ( if there is one ) to not allow, for example, the license plate MMG-561. So I guess I a asking for a “not equal” format. Maybe the key to understanding that is why your <> condition works, but my various tries at things of the form <>"MMG-561" do not seem to.. Could you explain how your <> is working to not allow empty cells. I have tried various variation of the form ="<>MMG-561" but with no success so far.
. Sorry if the question are trivial, but the on line help and examples all seem to lack precise details for the format :confused:
. Thanks
Alan
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Re: Using CriteriaRange to exclude blank cells in a Range with AdvancedFilter XLS2003 SOLVED

It would be like this:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">NumberPlate</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";"><>MMG-561</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />
 

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
Re: Using CriteriaRange to exclude blank cells in a Range with AdvancedFilter XLS2003 SOLVED

It would be like this:

Excel 2010
E
1NumberPlate
2<>MMG-561

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1
Thanks again Andrew.

. I think it is difficult from the screen shots to see exactly what you have typed in
-. I have tried for example typing in exactly

<>MMG-561

And

="<>MMG-561"

: but they do not work

. Note for your example in post #2 I must type in exactly

="<>"

.. But screen shot gives me if I select values :

Using Excel 2007
-
C
30
NumberPlate
31
<>
unfiltered

But if I select formulas I get

Using Excel 2007
-
C
30
NumberPlate
31
="<>"
unfiltered
 

Forum statistics

Threads
1,084,878
Messages
5,380,415
Members
401,673
Latest member
Ali Balleya

Some videos you may like

This Week's Hot Topics

Top