Mike F. wrote...
....
I am dealing with "empty" cells that are that way because they were unused,
and in other cases because a formula returned ="". If you do a copy of these
cells, and do a Paste > Special > Values I would expect the result to be
controllable, but it isn't. P>S>V puts a null string in the target cell if
the source was a formula that evaluated to ="", and it will put an "empty"
cell if the source was empty (cleared).
Converting cells from formulas to values should leave other formulas
that refer to those cells unchanged. For example, if A1 contained
=2*ROW(), and A2 contained =A1+1, then A2 would evaluate to 3. If A1 is
converted to its formula's value, A2 should still evaluate to 3. By the
same toekn, if B1 contained =LEFT(A1,0), so evaluated to "", and B2
contained =AND(LEN(B1)=0,COUNTA(B1)=1), B2 would evaluate to TRUE. If
B1 were replaced with its formula's value, then B2 should still
evaluate to TRUE. However, that requires that B1 *not* be blank
('Empty' means something precise in VBA, but not in cell formulas; in
cell formulas, 'blank' means the state of cells that contain nothing),
and that requires replacing anything evaluating to "" with something
rather than nothing.
This is complicated by the fact that Excel converts blank cells to
numeric zeros, 0, in numeric contexts and to zero length strings, "",
in text contexts. Lotus 123 did much better by *always* treating them
as 0 and *never* as "". In programming languages, consistency is always
a good thing.
Back to pasting ="" and equivalent formulas as values. The result is
one of Excel's odd possible values, the zero length text constant.
This causes three problems: 1) These cells do not test the same as a "real"
empty cell, 2) the "null string" cell does not allow the preceding cell text
to flow into the following cell, and 3) these two types of cells do not Sort
together nor do Filters and Pivot Tables treat them the same.
....
Replace 'empty' with 'blank'. Empty is necessarily ambiguous. You may
know what you mean, but your usage isn't tied to Excel's documentation.
These 'problems' are all well-known. They're predictable functionality
that others may rely on to behave as they currently do. Your #2 is, in
fact, sometimes desirable.
The final slap in the face is that if you press F2 and then enter on the
"pasted null string" cell, it changes to an empty cell!
No different than entering the formula
="=1+2"
copying, pasting as value on top of itself, then pressing [F2],
[Enter]. Or for that matter, entering =RAND() in a cell originally with
number format General, then changing the cell's number format to Text
and pressing [F2], [Enter]. The point is that re-entering cells *can*
change their types and values even if no changes are made to the cell's
contents.
I can accept, begrudgingly, that this is "the way Excel works". But there
has to be some type of EQUALIZER -- either function or VBA, that will let me
go through 27,000 rows and 38 columns of data and make all the "visually
empty" cells act the same. If Paste Values doesn't do it, then the only
alternative is to press F2-Enter on every cell. :-O (or read the whole
thing in and write it back out to a different sheet with VBA that does the
correct checks on each cell.
There's always macros.
Sub foo()
Dim r As Range
Application.Calculation = xlCalculationManual
For Each r In ActiveSheet.UsedRange
If r.Formula = "" And r.PrefixCharacter = "" _
And Not IsEmpty(r.Value) Then r.ClearContents
Next r
Application.Calculation = xlCalculationAutomatic
End Sub
