removeing a ' symbol

[Guest]

I am trying to get rid of a ' symbol with out doing it manually. find
replace doesn't work. i tried
With ActiveCell.Characters(Start:=1, Length:=1).Delete, but the deletes the
' as well as the next character. any ideas?

 

[Guest]

hi,
i think you have launched yourself into an endless
tailchase.
I don't know why you are trying to remove the appostophe,
but excel may have put it there.
excel uses this symble to 1. mark it as text or 2.left
justify.
In any cell in excel, type anything. if it is a number, no
appostrophe. if it is letters, appostrophe and left
justified. next center the text. ' changed to ^. next
right justify the text. ^ changed to =.
excel puts the symble in and there is not much you can do
about it. excel does not consider this symbles as a
character which is why find and replace didn't work.
and why ActiveCell.Characters(Start:=1, Length:=1).Delete
seemed to act funny.
and even if you did get rid of, excel would put it right
back.
Why are you trying to remove it?

 

[Dave Peterson]

How about...

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeConstants, _
xlTextValues))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Select some cells with constant text"
Exit Sub
End If

For Each myCell In myRng.Cells
myCell.Value = myCell.Value
Next myCell
End Sub