How to use AdvancedFilter but keep the rows with blank cells in it? 1

[feipezi]

Hi,

Here is the code:


....
Sheets("test").Range("test") _
.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("crt").Range("E11:m12"), _
CopyToRange:=Sheets("destination").Range("A17:BA17"), _
Unique:=False
....


The code won't keep the rows with blank cells in it. That's not what I wanted. I want to keep all the rows that satisfy the Criterion, doesn't matter if they have blank cells in it or not.

I have managed to fill the blank cells with "*", so the rows with "*" got picked up. But I've tried to take the rows without filling "*" but no luck.

Thanks in advance.

 

[SkipVought]

hi,

Did you try doing what you want on the sheet, sans VBA?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[feipezi]

Yes, Skip. I did. It gave me the same thing: rows without blank cells in it.

Thanks again.

 

[SkipVought]

So please state you criteria in prose and post a sample of your DATA and current CRITERIA as applied to your sheet.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[feipezi]

Hi Skip,
Criterion: $A$1:$B$2, i.e., I'd like to select OU=1X:EAST, Region=1XA:BOSTON.
This is what part of the data look like.
Thanks again.

ou region pod district
1X:EAST 1XA:BOSTON 1XAAA:BANGOR 1XAA:MAINE / NH / VT
1X:EAST 1XA:BOSTON 1XAAD:CONCORD 1XAA:MAINE / NH / VT
1X:EAST 1XA:BOSTON 1XABA:LOWELL 1XAB:WORCESTER
1X:EAST 1XA:BOSTON 1XABB:WORCESTER
1X:EAST 1XA:BOSTON 1XACA:BOSTON NORTH 1XAC:BOSTON
1X:EAST 1XA:BOSTON 1XACC:BROCKTON 1XAC:BOSTON
1X:EAST 1XA:BOSTON
1X:EAST 1XA:BOSTON 1XAGB:STAMFORD 1XAGANBURY
1X:EAST 1XA:BOSTON 1XAGB:STAMFORD 1XAGANBURY
1X:EAST 1XB:NEW YORK 1XBAA:BUFFALO EAST 1XBA:BUFFALO
1X:EAST 1XB:NEW YORK 1XBBC:ROCHESTER EAST 1XBB:ROCHESTER
1X:EAST 1XB:NEW YORK 1XBCA:SYRACUSE 1XBC:SYRACUSE
1X:EAST 1XB:NEW YORK 1XBDA:ELMIRA
1X:EAST 1XB:NEW YORK 1XBDB:BINGHAMTON
1X:EAST 1XB:NEW YORK 1XBFBOUGHKEEPSIE 1XBF:WHITE PLAINS
1X:EAST 1XB:NEW YORK 1XBGA:YONKERS 1XBG:BRONX
1X:EAST 1XB:NEW YORK 1XBGB:BRONX 1XBG:BRONX
1X:EAST 1XB:NEW YORK 1XBHA:UPPER MANHATTAN 1XBH:UPPER MANHATTAN
1X:EAST 1XB:NEW YORK 1XBJA:MANHATTAN MID-TOWN 1XBJ:LOWER MANHATTAN
1X:EAST 1XB:NEW YORK 1XBJB:LOWER MANHATTAN 1XBJ:LOWER MANHATTAN

 

[SkipVought]

So what's the problem?

What about the BLANK that you've been talking about? It filters INCLUDING the rows with empty values in the non-criteria columns???

HOWEVER, if you DID do the Advalced Filter on the sheet, you would have discovered that you cannot specify a different sheet!

1) NAME your filter criteria range, headings and all as Criteria

2) NAME the TOP LSFT CELL of your destination on the other sheet as FilterDest

Then...

    YourSourceDataSheetObject.Range("A1").CurrentRegion.AdvancedFilter _
        Action:=xlFilterCopy, _
        CriteriaRange:=[Criteria], _
        CopyToRange:=[FilterDest], _
        Unique:=False

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[feipezi]

Sorry about the confusion.
If I did it without using macros, fine.
The CriteriaRange and CopyToRange must use NamedRange?

I tried the data that I sent you using macros. It gave me the same output as using AdvancedFilter dropdown on the sheet (no macros, all the rows with blank cells got picked up). So there must be some problems with the data I used before (not the one I sent you).
I'll keep checking that.

Thanks again for your time.

 

[SkipVought]

I am confused!

The code won't keep the rows with blank cells in it. That's not what I wanted. I want to keep all the rows that satisfy the Criterion, doesn't matter if they have blank cells in it or not.

THAT is EXACTLY what the Advanced Filter does: Code ot not!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[feipezi]

Sorry about the confusion.

I'm not 100% sure but it looks like the test data I sent you is diff. from the data that I have problems with.

One of the reasons is that the data I sent you have " " in the blank cells; but the data with the problems have "" in those cells. I used the following code to test and it was right.

I selected a whole bunch of cells with no contents in them. They were supposed to satisfy the criterion specified at the beginning of the post.



Sub tnt()
For Each c In Selection
If IsEmpty(c.Value) Then MsgBox c.Address
Next
End Sub





Please ignore what I said above. I tested again. The rows with empty cells in it have been picked up, not like the question that I raised before.

I don't know what's going on but I'll try to find out.

Thanks again.