Blank cell is not blank

  • Thread starter Thread starter Otto Moehrbach
  • Start date Start date
O

Otto Moehrbach

Excel XP & Win XP
This one is costing me hair.
I am helping an OP from England.
I am working with her file.
I narrowed down my problem so I can explain it simply and without getting
into VBA.
I have a string of cells in a row that appear to be blank.
I select a cell to the left of my problem cell and do End - Right arrow.
Excel stops at the problem cell, that appears to be blank.
I do =Len(That cell) and get zero.
I do =CountA(That cell) and get 1.
All the cells have Conditional Formatting as a function of row number with a
format of color. Most of the "empty" cells act like empty cells. But a
few, like my problem cell, do not.
The code "If ActiveCell.Value="" Then..." says it's True.
The code "If IsEmpty(ActiveCell.Value) Then... says its False.
I can do Edit - Clear - Contents and the problem is solved.
I know I can run a code that loops through all the cells in the used range,
like:
If i.Value="" Then i.ClearContents
and solve this problem, but my question is: What is happening with that
cell? Thanks for your time. Otto
 
Otto,

I don't know if it helps but if I enter the formula ="" in a cell then
copy/paste special back into the cell I get the same results that you are
reporting.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
Hi Otto,

I suspect the following:
The cell is not empty in the sense of IsEmpty, because there is a formula in it
The cell is "" because the result of the formula is ""

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Excel XP & Win XP
| This one is costing me hair.
| I am helping an OP from England.
| I am working with her file.
| I narrowed down my problem so I can explain it simply and without getting
| into VBA.
| I have a string of cells in a row that appear to be blank.
| I select a cell to the left of my problem cell and do End - Right arrow.
| Excel stops at the problem cell, that appears to be blank.
| I do =Len(That cell) and get zero.
| I do =CountA(That cell) and get 1.
| All the cells have Conditional Formatting as a function of row number with a
| format of color. Most of the "empty" cells act like empty cells. But a
| few, like my problem cell, do not.
| The code "If ActiveCell.Value="" Then..." says it's True.
| The code "If IsEmpty(ActiveCell.Value) Then... says its False.
| I can do Edit - Clear - Contents and the problem is solved.
| I know I can run a code that loops through all the cells in the used range,
| like:
| If i.Value="" Then i.ClearContents
| and solve this problem, but my question is: What is happening with that
| cell? Thanks for your time. Otto
|
|
 
If the =len() formula returns a 0, then my bet is that you used to have a
formula in those "blank" cells. They evaluated to "".

like: =if(a1=3,"",a1+7)

Then you did a copy|paste special|values.

This leaves the cell looking blank, but it's not.
(Try =isblank() against one of the offending cells.)

And you can see the "detritus" left behind by toggling a setting.

Tools|options|transition tab|check transition navigation keys.

You'll see an apostrophe in the formula bar with that cell selected.

===
I like this way to clean up that type of "blank" cell.

select the range (or the whole sheet)
edit|replace
what: (leave blank)
with: $$$$$ (some unique value--not used!)
Replace all

Then reverse it:
edit|replace
what: $$$$$ (that same value)
with: (leave blank)
Replace all


==
If you're using VBA, you could record a macro when you do those two Edit|Replace
and plop that into your code to clean up that junk.
 
It appears that you hit it on the head. The cell contains a carrot (Shift -
6). Perhaps that's a British version thing. I ran a loop with the code:
If i.Value="" Then i.ClearContents
through the used range and solved the problem. Thanks. Otto
 
There were some old Lotus shortcuts that excel will use (with that setting
toggled):

^asdf (will center asdf in the cell)
"asdf (will right justify asdf in the cell)
 
Thanks Dave. Otto
Dave Peterson said:
There were some old Lotus shortcuts that excel will use (with that setting
toggled):

^asdf (will center asdf in the cell)
"asdf (will right justify asdf in the cell)
Click to expand...
 
Back
Top