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>
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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.
 
Upvote 0
Re: Using CriteriaRange to exclude blank cells in a Range with AdvancedFilter XLS2003 SOLVED

Thanks guys - worked perfectly!
 
Upvote 0
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-...et-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
 
Upvote 0
Re: Using CriteriaRange to exclude blank cells in a Range with AdvancedFilter XLS2003 SOLVED

Can't you use the criteria?

???-???
 
Upvote 0
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
 
Upvote 0
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
Sheet1
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top