Foolproof Advance Filter criteria - blank cells

Hi All,

This is a sort-of bizarre query I have.

I have a large array of data which I'm filtering out and copying to a new spreadsheet using an advanced filter. I have 2 filter criteria, one works... the other doesn't.

The second criteria is supposed to filter out rows that have blank cells in column C or D. I tried various things:

a. at first I tried following:
Column header: Name |Surname
Criteria: <>"" |<>""

b. since it didn't work I tried that
Column header: Name |Surname
Criteria: =<>""""" |=<>"""""

I also tried <>0 and <>null with absolutely no joy.

c. once I figured out that doesn't work either, I tried:
Column header: (empty)
Criteria: =OR(NOT(ISBLANK(C2)),NOT(ISBLANK(D2)))

now, the last one did have some effect, most of the empty-celled rows have been filtered out. However the filter persistently picks up ONE row that has blanks in column C2 and D2. I checked the data to check if the cell is really blank and it's as blank as it gets.

Obviously, I need help. It's been 3 days and I still can't figure out what's wrong with my filter. Is there a way of fool proofing the criteria, so that it will filter out all cells that are or appear to blank?

Thanks
Seler
(PS. no, I don't have single quotes in those cells. Nor spaces. they are really very blank.)

Hi,
if you use the Autofilter, does it recognize the "blank" cells as being "blank" ?

Hi arthurbr,

Yes, it does recognize it as a blank. Autofilter is perfectly comfy with my data.

JJ

davesexcel - Obvious reason - I need it for a bigger thing! I only want certain columns to be returned in a different spreadsheet AND I need to have it done 156 times, so this filter is a part of a macro that does it for me...

As long as the filter returns blank cells in columns C and D a part of the macro will not work.

As well, for the sake of principle - why would autofilter be a solution when I WANT to use advance filter? shouldn't it work just as well?

Seler

why would autofilter be a solution when I WANT to use advance filter? shouldn't it work just as well

Well it doesn't seem to ;-)

Well it doesn't seem to ;-)

You can't imagine how Delighted I am that it doesn't :> Just the fact I have to deal with it makes me feel nauseous....

Seler, could you post a small sample of your data including the " blank cells" ?

Hi Roy,

I have no problem with Hiding the data, I have a problem with extracting data to another sheet based on criteria of the filter. Hiding it doesn't make much of a difference, you know.

Hi arthurbr,

I wouldn't feel entirely comfortable posting the data for the whole world wide web to see - while it's not strictly confidential, it is rather sensitive. Would it be ok with you if I sent it to you via email?

Cheers,
Seler

Hi Guys,

Just so you know - I managed to resolve it by adding following piece of code into the macro:

Please Login or Register  to view this content.

This searches for all empty cells in colums B and C of the data in the filter results and deletes entire row if an empty cell is found.

a bit of an overkill, but works...

Cheers,
Seler