EXCEL 2003 paste special and go to specil misfunction

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
 >> Stay informed about: EXCEL 2003 paste special and go to specil misfunction 

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

Jack wrote:
>
> 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.

--

Dave Peterson
 >> Stay informed about: EXCEL 2003 paste special and go to specil misfunction 

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).

Shane Devenshire wrote:
>
> 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
>
> "Jack" wrote:
>
> > 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.
 >> Stay informed about: EXCEL 2003 paste special and go to specil misfunction 

Thank u all very much, as soon as I try all of them i'll give u my feedback
(if u want it )
 >> Stay informed about: EXCEL 2003 paste special and go to specil misfunction