empty cells - best way of testing for them

[Chris Strug]

Hi,

I have a simple loop in which I loop through a column of cells.During each
iteration I wish to test to see if the cell is empty (has no value, which is
to say, it might have once had a value and been deleted, it may contain an
empty string or it may simply have never been used). However I'm unsure of
the best way to do this.

For example, the isnull function appears to recognise empty strings as not
null values (for example if that cell has at one point contained data and
has now been cleared).

I have recently discovered the IsEmpty function that appears to do what I
want which is detect when a cell contains no data - be that a null or an
empty string.

However I don't know much about the function - while it appears to work is
it the best way to test for an empty cell.

Any and all assistance is gratefully received.

Kind thanks

Chris Strug.

 

[Jim Cone]

Chris,

I prefer to use the Len function to check for blank/empty worksheet cells....

If Len(MyCell) then
'do something
End if

It seems to be faster than other methods and has the benefit of ignoring " ' " - the single apostrophe

Regards,
Jim Cone
San Francisco, CA

 

[Dave Peterson]

I think it's the safest way to really test if the cell is empty.

If it's a formula that returns "":
=if(a1>3,"big","")

then this cell isn't empty, but the length of the cell's value would be 0.

 

[Chris Strug]

I think it's the safest way to really test if the cell is empty.

If it's a formula that returns "":
=if(a1>3,"big","")

then this cell isn't empty, but the length of the cell's value would be 0.

Dave / Jim,

Thanks for the reply. On balance I think I'll just test the length of the
cell value but thanks to you both for your advice.

Cheers

Chris.