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?

 

[Saurabhj]

Hi, have you checked in different sheet ?

 

[tiredofit]

Hi, have you checked in different sheet ?

Yes, worked as expected.

 

[JLGWhiz]

The IsEmpty function is designed to test if a data variable has been initialized, and not to check if a cell is blank. (although many people use it for checking if a cell is blank). The best way to check if a cell is blamn is to see if its value equal nullstring (""). Here is the spec sheet link for your edification.
IsEmpty function (Visual Basic for Applications) | Microsoft Docs

 

[Saurabhj]

Seems it's taking it as Null String. What is the result of below code:

MsgBox IsNull(Sheet1.Cells(1, 1).Value)

 

[tiredofit]

The IsEmpty function is designed to test if a data variable has been initialized, and not to check if a cell is blank. (although many people use it for checking if a cell is blank). The best way to check if a cell is blamn is to see if its value equal nullstring (""). Here is the spec sheet link for your edification.
IsEmpty function (Visual Basic for Applications) | Microsoft Docs

Thanks.

The real reason for all this is I want to check how many non blank cells in a particular row.

By eye, it looks like 3 but when I type:

?application.WorksheetFunction.CountA(rows(1))

I get more than 3.

So I proceeded to inspect each cell in that row.

I tried your suggestion and typed:

?wksq4.Cells(1,7).value=vbnullstring

and it returned True.

I did the same for all remaining cells in row 1 and they all returned True, so how can:

?application.WorksheetFunction.CountA(rows(1))

return anything other than 3?

 

[tiredofit]

Seems it's taking it as Null String. What is the result of below code:

MsgBox IsNull(Sheet1.Cells(1, 1).Value)

False

 

[JLGWhiz]

If there is a formula in the cell that produces a nullstring as one of its options when all other oprions fail, and you test with IsEmpty, you will get the False return.
But if you test for the length of the value in the cell, you will get zero because Len only checks for displayed values and not for formulas.

 

[tiredofit]

If there is a formula in the cell that produces a nullstring as one of its options when all other oprions fail, and you test with IsEmpty, you will get the False return.
But if you test for the length of the value in the cell, you will get zero because Len only checks for displayed values and not for formulas.

so what should I do?

 

[tiredofit]

I tried a "trick".

As I said, I can only see values in columns A, B and C.

So on cell A1, I pressed Ctrl+right arrow and it jumped to cell C1, which I interpret to mean cell D1 is blank.

Now on cell C1, I press Ctrl+right arrow and now it jumps to cell G1, which I interpret to mean cells D, E and F are blank but G isn't.