This seemed to work ok:

Option Explicit
Sub testme02()

Dim FoundCell As Range
Dim ConstCells As Range

With Worksheets("sheet1")
Set ConstCells = Nothing
On Error Resume Next
Set ConstCells = .Cells.SpecialCells(xlCellTypeConstants, _
xlTextValues)
On Error GoTo 0
If ConstCells Is Nothing Then
MsgBox "No Text Constants on this sheet!"
Exit Sub
End If

With ConstCells
'get as many as we can in one step
.Replace what:=Chr(34), replacement:=Chr(39), _
lookat:=xlPart, searchorder:=xlByRows

Do
Set FoundCell = .Cells.Find(what:=Chr(34), _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlPart, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
'done, get out!
Exit Do
End If

FoundCell.Value = Replace(FoundCell.Value, Chr(34), Chr(39))

Loop
End With
End With
End Sub

Chr(34) is a double quote: "
chr(39) is a single quote: '

If you're using xl2k or higher, you can change this logical line:
FoundCell.Value = Application.Substitute(FoundCell.Value, _
Chr(34), Chr(39))
to
FoundCell.Value = Replace(FoundCell.Value, Chr(34), Chr(39))

(Replace was added in xl2k.)

Nurddin wrote:

Hi,

I need to find double quotes in an excel file (could be a selectoin of
rows) and replace them with nothing ( yeah just want to delete them)

The built in Excel function to Find and Replace all gives me error
"formula too long"

Can someone please write me a small macro code

thanks

--

Dave Peterson