0

I have successfully used the following to filters for ="=" (Blanks only) and ``="<>" (Non-blanks only)

However it isn't working with a large range for some reason. If I look for a value, ie ="*CC"then the filter is applied as expected.

Any ideas as to why my Blank and Non-Blank filters might be failing?

Cheers

** LATEST UPDATE ** Am pretty sure the underlying issue is that the value of the column I want to filter is the result of a formula! Anyone know how to handle that?

** UPDATE ** This is a partial view of the data I want to filter (it's a ListObject) enter image description here

Here is the criteria range. It's located on a separate worksheet (which shouldn't matter, and again, is working fine searching for a given value (ie "LNG")

enter image description here

And finally, this is a picture of the desired result of filtering out blanks: enter image description here

I can easily get this with AutoFilter of course, but this is a large table and I am using VBA to switch between different analytical views. The AdvancedFilter is more appropriate in this context.

** UPDATE ** I have the same issue whether I apply the filter manually or via code, which further leads me to believe there is something flaky about the filter itself. The code has been successfully tested, and so in fact has the filter on a simple table.

I tried changing the type of the 'Position' column cells (currently it's Text). Just a head scratcher at the moment

Improve this question
8
  • your question isn't clear now, please explain better where the criteria doesn't work. (both range description and function where you use it) Some sample data would help too.
    – Máté Juhász
    Dec 21, 2015 at 12:54
  • @MátéJuhász. See update
    – Berryl
    Dec 21, 2015 at 13:35
  • thanks. And where it fails? do you have any pattern when filtering doesn't work as expected? Do you have the same issue also when you set the filter manually or only through the macro? (if it's only VBA related, then please also post it's part where filtering is done) ... These are all necessary to understand your problem.
    – Máté Juhász
    Dec 21, 2015 at 13:38
  • @MátéJuhász. See update
    – Berryl
    Dec 21, 2015 at 13:51
  • Does =<>"" give any better results?
    – Kyle
    Dec 21, 2015 at 16:37

1 Answer 1

Reset to default
0

PROBLEM: The value in the column I want to filter on is the result of a formula which either gives a value or an empty string "".

Using ="<>" is the right way to get Non-Blank but Excel is not considering the empty string to be Blank!

A SOLUTION: For lack of knowing a criteria friendly way of saying something like ="vbNullString", I just added another column which evaluates the "Y" if the column I really want is not blank. After hiding the column, I can Filter on that and get the results I need.

Please post if you know a way to get Excel to recognize the empty string tho!

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .