empty cells - best way of testing for them
 

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.

empty cells - best way of testing for them
 

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

"Chris Strug" wrote in message ...
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.

empty cells - best way of testing for them
 

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

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

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



Chris Strug wrote:

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.

--

Dave Peterson

empty cells - best way of testing for them
 

"Dave Peterson" wrote in message
...
I think it's the safest way to really test if the cell is empty.

If it's a formula that returns "":
=if(a13,"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.