EXCEL 2003 paste special and go to specil misfunction

[Jack]

Hello to everybody,
I noticed that if I use the "paste special - values" function to, say, paste
three cells with a formula behind and just one value displaying, your result
would be to obtain two blank cells and a cell with a value on it.
The problem is, the cell isn't completely blank. In fact, using the "got to
special" function (from menu "edit - go (F5) - special") and I selecting the
option "blank cell" I don't get back back a selection of the two "blank"
cells I was expecting of.

The problem (that I couldn't actually find on the web) is that there is a
sort of a hidden formula in these "blank" cells.

Thanks in advise.

 

[Jim Rech]

Yes it's true that if you paste special, values a formula that returns
nothing like ="" you get what is called a "null cell". They re annoying but
you cannot change Excel's behavior. I wrote a macro some years ago that
deletes nulls in the selected range:

Sub ClearNullsInSelection()
Dim CurrCell As Range, CurrCol As Range
Dim EraseRg As Range
Dim NullCounter As Long
For Each CurrCol In Selection.Columns
Application.StatusBar = "Doing column " & CurrCol.Address
For Each CurrCell In CurrCol.SpecialCells(xlCellTypeConstants)
If Len(CurrCell.Formula) = 0 And Not IsEmpty(CurrCell) Then
NullCounter = NullCounter + 1
If EraseRg Is Nothing Then
Set EraseRg = CurrCell
Else
Set EraseRg = Union(EraseRg, CurrCell)
End If
End If
Next
If Not EraseRg Is Nothing Then
EraseRg.ClearContents
Set EraseRg = Nothing
End If
Next
Application.StatusBar = False
If NullCounter > 0 Then
MsgBox NullCounter & " null cells cleared"
Else
MsgBox "No null cells found"
End If
End Sub

 

[Dave Peterson]

Just to add to Jim's response:

Saved from a previous post:

If you want to see what's left in that cell after you convert ="" to values,
try:
Tools|Options|Transition Tab|Toggle Transition Navigation keys on.

Then select one of those cells and look at the formula bar. You'll see an
apostrophe. (Don't forget to toggle the setting to off.)

When I want to clean up this detritus, I do this:

Select the range (ctrl-a a few times to select all the cells)
Edit|Replace
what: (leave blank)
with: $$$$$
replace all

Immediately followed by:
Edit|Replace
what: $$$$$
with: (leave blank)
replace all

In code you could do something like:

Option Explicit
Sub testme()
With ActiveSheet
With .cells 'or a specific range: With .Range("D")
.Replace what:="", replacement:="$$$$$", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
.Replace what:="$$$$$", replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
End With
End With
End Sub

 

[Shane Devenshire]

Hi,

One way to handle this is to change the original formula from something like
this
=IF(A1>10,A1,"")
to read something like
=IF(A1>10,A1," ")
note the space

Then after the paste values command, press Ctrl+H and press spacebar in the
Find what box and enter nothing in the Replace with box. Choose the More
option of Entire cell contents, then Replace All.

If this helps, please clich the Yes button

cheers,
Shane Devenshire

 

[Dave Peterson]

Just a variation...

When I know I'm going to convert my formulas to values and I don't want that
junk left behind, I'll use a variation of Shane's formula:

=if(a1>10,a1,na())

Those #N/A's are impossible to ignore (so I won't forget).

 

[Jack]

Thank u all very much, as soon as I try all of them i'll give u my feedback
(if u want it )