Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index/match - make blank cells return a blank value. | Excel Worksheet Functions | |||
Excel 2002: Return blank when VLOOKUP on blank cells | Excel Discussion (Misc queries) | |||
"Calculating Cells" Mystery | Excel Discussion (Misc queries) | |||
Maximum Number of Blank Cells between Non Blank Cells in a Range | Excel Worksheet Functions | |||
Mystery: System Error and blank squares | Excel Discussion (Misc queries) |