Holloween Mystery: ghostly blank cells
 

This one will have you whistling past the graveyard.

Cells in C1:C10 have a simple counting formula if an
adjacent cell is non-blank. At some point my code Copies
and Paste Specials the formula range, so that the formulas
are replaced by their numeric values.

Now the goblins appear. Lets say C1:C5 have numbers, but
C6:C10 have nothing because the formula says if the
adjacent cell is blank, give this cell a "". When I sort
C1:C10 in Descending order, the "blank" ("") spaces go to
the <top of the sorted list. OK, I thought that the blank
cells aren't really blank, so I checked their Len(). It
was 0, so they really <are blank. But still they go to
the top, so they must contain a hidden <something. But
what is it? Isn't a cell defined as "" a blank?

How do I make the blank goblins behave, and stay at the
bottom of the sort???

TIA
Paul

Holloween Mystery: ghostly blank cells
 

They contain a null string which isn't blank.

Rather than do a paste special do
for each cell in Range("C1:C10")
if len(trim(cell)) = 0 then
cell.ClearContents
else
cell.Formula = cell.Value
end if
Next

--
regards,
Tom Ogilvy

"Paul" wrote in message
...
This one will have you whistling past the graveyard.

Cells in C1:C10 have a simple counting formula if an
adjacent cell is non-blank. At some point my code Copies
and Paste Specials the formula range, so that the formulas
are replaced by their numeric values.

Now the goblins appear. Lets say C1:C5 have numbers, but
C6:C10 have nothing because the formula says if the
adjacent cell is blank, give this cell a "". When I sort
C1:C10 in Descending order, the "blank" ("") spaces go to
the <top of the sorted list. OK, I thought that the blank
cells aren't really blank, so I checked their Len(). It
was 0, so they really <are blank. But still they go to
the top, so they must contain a hidden <something. But
what is it? Isn't a cell defined as "" a blank?

How do I make the blank goblins behave, and stay at the
bottom of the sort???

TIA
Paul