Advanced Filter sort on "asterisk" (wild card) hides data, not just empty blank cells.

[Cloudfall]

In debugging a program, I isolated the problem to the following issue.
I manually created the following situation in column A, rows 1 to 6, by
copying and pasting actual data in order to accurately re-create the
problem:

ABN/ACN/BN
*

ABN/ACN/BN
3158816
40000545415/005

Excel "Help" has this to say about the asterisk:
" * (asterisk) Any number of characters in the same position as the
asterisk
For example, *east finds "Northeast" and "Southeast" "

I am assuming that the asterisk, being the wild card symbol, being the
"cell not empty" symbol, will result in the display of both the strings
under the title ABN/ACN/BN (that is, "3158816" and "40000545415/005").
But no! The string "3158816" mysteriously disappears, leaving just
"40000545415/005". The "List Range" and "Criteria Range" appear
correct.

Would anyone mind explaining to me what horrible assumption I have made
now, why on earth this is happening, and what I need to do to correct
it? And, if anyone has the time, would someone mind explaining to me
how I could have worked this out for myself and not bothered you good
people? I really am trying to be self-sufficient here. Thanking you in
anticipation.

 

[Dave Peterson]

You assumed that the asterisk worked with real numbers, too. If your 3158816
were entered as text ('3158816), then you would have seen what you wanted.

If you're trying to hide the truly empty cells (not formulas that evaluate to
""), you could use a criteria of:

ABN/ACN/BN
<>



If you're trying to hide any cell that looks empty (including the formulas that
evaluate to ""), something like this should work:

I put my test data in A17
with ABN/ACN/BN in column C.

I used this criteria in G1:G2
G1 (leave empty)
G2: =LEN(C2)>0

(And filtered on A17.)

========

And experimentation is a good way to find out what happens--And google, too.

 

[Debra Dalgleish]

The * character works on text strings, but not numbers, so it doesn't
return 3158816. If 3158816 is entered as text, it will be returned.

 

[Cloudfall]

Thank you.

 

[Cloudfall]

Mr Peterson,

Thank you again for your help. I did experiment and I reduced your
suggestions to their minimum for purposes of demonstration and proof. I
read on another post that those who help enjoy having some feedback.
Viola.

"List Range" data is all the same and is as follows:

HeadingX
1
a
'formuala evaluated to zero-length string
'empty cell
2
b

The following hides numbers so you get the following result:

HeadingX
* 'Criterion

HeadingX
a
'formuala evaluated to zero-length string
b

The following hides the empty cell so you get the following result:

HeadingX
<> 'Criterion

HeadingX
1
a
'formuala evaluated to zero-length string
2
b

The following hides the empty cell and the cell containing the
zero-lenght string so you get the following result:

HeadingY 'note heading must be different somehow
=LEN(A6)>0 'criterion is formula in cell which displays as "TRUE"

HeadingX
1
a
2
b

For feedback, Excel Help does not tell me about about using <> for
hiding empty cells (that I could find), nor could I find anywhere else
using Google that mentions or discusses it. The above will be of use to
me if I ever forget about this issue again which is why I have
documented it to the extent I have. I'd like to make the comment that
Advanced Filter is not user friendly, and that it took me a long time
to understand what Excel Help was trying to tell me when I was trying
to learn how to use it.

Thank you again for your help.

 

[Dave Peterson]

Thanks for you feedback.

And your notes will be available to any who search via google (so that's nice).

And everytime I used Advanced filter (except for unique values), I have to do
experimentation to get it close to right.

 

[Cloudfall]

One final point. Make sure Tools, Options, Transition, "Transition
navigation keys" checkbox is unchecked or it will not work properly.

 

[Cloudfall]

Surely after all was said and done above I might have gotten this
right, but I haven't. I have copied what is below directly from Excel:

C
FALSE 'this cell contains "=LEN($B$5)=0"

A B
1
B
2

I expected to get this result (filtering on empty cells in column B):

C
FALSE 'this cell contains "=LEN($B$5)=0"

A B
B

Instead I got this result:

C
FALSE 'this cell contains "=LEN($B$5)=0"

A B

Nothing survived the filter. Why? I've been on this for two days now.
Thank you now for any help offered.

Yours in perplexity.

 

[Dave Peterson]

It's difficult to know where your data is and where your criteria range is.

I put this in B1:B5
myHeader
1
2
(blank)
=""
3

I put this in D12
(blank)
=len(b2)=0

I applied data|filter|advanced filter:
List Range: B1:B5
Criteria range: D12

And only the cells that looked empty appeared.

 

[Cloudfall]

The criteria are in B1:B2 and the List Range are in A4:B7.

OK, I know what was causing the problem. I had "LEN($B$5)=0" instead of
"LEN(B5)=0". I don't remember why I did it this way but I've changed it
and it works properly now.

Thank you for staying with me on this. I'm relatively new to VBA for
Excel. The language is cryptic and vast. A mistake leaves me
figuratively beating my head against a brick wall, not knowing what to
do.

Yours in gratitude,

Terry R.

 

[Dave Peterson]

Glad you got it working--and glad it's just figurative!

 

[Cloudfall]

This is just not going away!

OK, I got my demo of the problem working, but when I went to my actual
code I realised where I got "LEN($B$5)=0" from. In my code I originally
had:

range("E2").Select
ActiveCell.FormulaR1C1 = "=LEN(E5)=0"

This results in the following in cell E2:

=LEN('E5')=0

And this is what you see in the spreadsheet in the actual cell:

#NAME?

So then I changed my code to something like:

range("E2").Select
ActiveCell.FormulaR1C1 = "=LEN(R5C5)=0"

So that inserted "=LEN($5$5)=0" into the formula bar which, as we have
seen, doesn't work!

So what do I do now? How do I programmatically put "=LEN(E5)=0" into a
cell and have it work? Thanking all respondees now. The saga and
headbanging continues.

 

[Cloudfall]

I worked it out.

I used

ActiveCell.Formula = "=LEN(E5)=0"

instead of

ActiveCell.FormulaR1C1 = "=LEN(E5)=0"

and it worked.