"Blank" cell is not blank

[Otto Moehrbach]

Excel 2003, WinXP
I think I need some help with this one.
I am doing everything with VBA.
I have some 18 pages of data.
I copy the same column, from row 6 to the last entry, in each sheet and
paste it to the bottom of a list in a Utility sheet.
There are many blank cells in this copied data.
I sort the final list in the Utility sheet.
This puts all the blank cells to the bottom of the list. Great.
Except for one cell.
This cell has a (=Len) of 0.
(=IsBlank) says it is not blank.
I reset the name of the list and use that list in a Data Validation cell.
Data Validation sees that one cell as blank and you know what that does.

Interesting fact: I can select that one cell in the Utility sheet and do
Edit - Clear - All and a subsequent Sort treats that cell as a true blank
cell and puts it at the bottom of the list.

I can write a looping code to go through all the original cells in the 18
sheets, check that Len = 0, and if it does, execute an Edit - Clear - All
(in VBA), and clean up that one bad cell. Until the user does whatever to
mess up another cell..

Or I can do the same thing to the list in Utility sheet within the code that
sets up the list. This would run whenever the user resets the list.

My question is this: What is happening with this one cell?
Thanks for your help. Otto

 

[A.W.J. Ales]

Otto,

I think (haven't tested it in a sort, but the behaviour of the rest is the
same ; len=0 ; not blank) that your cell contains ' and nothing more.
It doesn't show, hasn't length, but the cell isn't blank

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[A.W.J. Ales]

Sorry,

A bit to hasty I think. it doesn't sort and it would show up (at least in
the formulabar).
And it wouldn't behave as you describe after a clear as well.


So this isn't the answer. Sorry again.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Guest]

Best guess. Someone is using the space bar to clear an
entry in the cell. The delete key leaves "". The space
bar leaves " " in the cell. This is too simple but worth
checking.

 

[Tom Ogilvy]

If you have a cell with a formula like

=if(A1<>"",A1,"")

Make A1 blank. then select this cell and do Edit=>Copy, followed by
Edit=>PasteSpecial selecting Value, you will get a cell that looks blank but
contains a null string. (a previous poster suggested you have this when you
clear or delete a cell, but that is incorrect). Clearcontents or clear
will remove the null string.

I would assume this is what you are encountering.

 

[Dave Peterson]

If Tom's description matches what you did, (copy|paste special|values), then one
way to get rid of these non-blank blank cells is to:

Edit|replace
Find What: (leave blank)
Replace With: $$$$$ (or some unique string)
Replace all

then do it once more to reverse it.

(record a macro to get the code if you need it.)

Another option if all your formulas are converted to text:

with activesheet.usedrange
.value = .value
end with

I like the replace method. The only difference I've seen is that the
..value = .value technique can change formatting for cells that have multiple
(character by character) formatting.

 

[Otto Moehrbach]

Thanks to all. I'll try the different suggestions and see what gets the
Sort to treat that cell as a true blank cell. Otto