Checking for empty

[tiredofit]

I check for emptiness by typing this into the immediate window:

?IsEmpty(Sheet1.Cells(1,1).Value)

and it returns False

But when I type:

?Len(Sheet1.Cells(1,1).Value

I get a value of 0!

Can someone tell me what is going on?

Is the worksheet corrupt?

 

[JLGWhiz]

You can use the worksheet functions 'CountA' and 'CountBlank' to check a range for cell content.

 

[tiredofit]

You can use the worksheet functions 'CountA' and 'CountBlank' to check a range for cell conCountA

CountA was the first thing I used and it gave more a value of more than 3.

Countblank though has returned the correct figure, 16381 because there are 16384 columns in Excel and I see 3 cells populated.

But I'm still curious why CountA failed.

 

[JLGWhiz]

CountA was the first thing I used and it gave more a value of more than 3.

CountA will return cells with formulas and non printing characters although they might not display a value.

 

[tiredofit]

CountA will return cells with formulas and non printing characters although they might not display a value.

Thanks.

This worksheet contained data downloaded from a system, so I suspect that might have played a part.

 

[Saurabhj]

Ah! missed this part - what the sheet contains...

Thanks for sharing outcome.

 

[JLGWhiz]

Thanks.

This worksheet contained data downloaded from a system, so I suspect that might have played a part.

Yes, that happens frequently with downloads from the web. Some non printing characters take exrra effort to identify and eliminate from Excel. Other things to watch for in the downloads is the double quote marks, if they are not ASCII 34. they can cause problems in code.

 

[tiredofit]

Yes, that happens frequently with downloads from the web. Some non printing characters take exrra effort to identify and eliminate from Excel. Other things to watch for in the downloads is the double quote marks, if they are not ASCII 34. they can cause problems in code.

a	b	c
FALSE​	FALSE​	FALSE​	TRUE​	TRUE​	TRUE​	FALSE​	TRUE​	TRUE​	FALSE​	TRUE​	=ISBLANK(L1)
0​	0​	0​	1​	1​	1​	1​	1​	1​	1​	1​	=COUNTBLANK(L1)

Thanks.

As can be seen in the screenshot, COUNTBLANK gives the correct answer, unlike ISBLANK.

 

[Fluff]

If Isblank returns False, then the cell is not blank, there is something in it, as shown when you did Ctrl Right Arrow.
What does this return

=CODE(G1)

 

[tiredofit]

If Isblank returns False, then the cell is not blank, there is something in it, as shown when you did Ctrl Right Arrow.
What does this return

=CODE(G1)

#VALUE!

For columns A, B and C, it returns 97, 98 and 99, all other columns returns #VALUE!

 

[Fluff]

What about

=UNICODE(G1)