Ghost Cells
 

I have a VBA procedure that loops through cells collecting information on the contents of cells with values greater than zero.

Sometimes the program will blow up when it reaches a particular cell that appears to be completely empty but which passes as 0 when the program looks at it.

I try to delete the contents of the cell but it still goes through as 0.

Any way to erase a ghost cell like this?

Ghost Cells
 

wrote:
I have a VBA procedure that loops through cells collecting
information on the contents of cells with values greater than zero.
Sometimes the program will blow up when it reaches a particular cell
that appears to be completely empty but which passes as 0 when the
program looks at it. I try to delete the contents of the cell but it
still goes through as 0. Any way to erase a ghost cell like this?

What do you do to "try to delete the contents"?

My guess: the cell value is a null string that might have been
copy-and-pasted-value. So you would do see any evidence of it. But note
that in Excel (and VBA), text is always considered greater than any
numerical value.

To clear the cell:
Range(...).ClearContents
or
Range(...).Clear (contents, formats, etc)

You might also try:
Activesheet.UsedRange

Usually, that resets Excel's idea of the where the last cell of the
worksheet is.

However, keep in mind that if the cell's format has changed -- even from
something back to General -- Excel considers the cell to be used, even
though it might not contain a value.

Finally, if you cannot eliminate the cell from the loop range, change your
test to:

If Range(...) < "" And Range(...) 0 Then
or
If Trim(Range(...)) < "" And Range(...) 0 Then

Of course, it would be more efficient to load Range(...) only once.

Ghost Cells
 

"joeu2004" wrote:
To clear the cell:
Range(...).ClearContents
or
Range(...).Clear (contents, formats, etc)

You might also try:
Activesheet.UsedRange
[...]
change your test to:
If Range(...) < "" And Range(...) 0 Then
or
If Trim(Range(...)) < "" And Range(...) 0 Then

If no one's suggests seem to work, upload an example Excel file to a
file-sharing website, and post the share/public URL in a new response in
this thead. The devil is in the details, often.