Search and Replace - Leading Apostrophe

[fbohlandt]

Hi all

I would like to search and replace for leading apostrophes. I have two pieces of code. The first recognizes the leading apostrophe but is slow in execution (loop through cells in range). The second is fast but ignores the apostrophe.

Code 1:

Sub RemoveApostrophe()   
For Each cell In Selection       
    cell.Value = Replace(cell.Value, "'", "")   
Next cell
End Sub

Code 2:

Sub FindReplaceAll()

Dim sht As Worksheet
Dim fnd As Variant
Dim rplc As Variant

fnd = "'"
rplc = ""

'Store a specfic sheet to a variable
  Set sht = Sheets("Sheet1")

'Perform the Find/Replace All
  sht.Cells.Replace what:=fnd, Replacement:=rplc, _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False

End Sub

Essentially, I am looking for a macro that runs over a fairly large range in a worksheet (~ 10,000 cells) and replace any leading apostrophes/high commas whilst minimizing runtime. I am not sure I can avoid addressing each cell individually but hoping there is another way.

Thank in advance

 

[offthelip]

If your worksheet only contains data and no formula then it will be much faster to load the entire worksheet into a variant array , process the worksheet in memory and then write it back.
if this is true : try this code:

Sub test()
inarr = Worksheets("sheet1").UsedRange
 For i = 1 To UBound(inarr, 1)
  For j = 1 To UBound(inarr, 2)
     inarr(i, j) = Replace(inarr(i, j), "'", "")
  Next j
 Next i
Worksheets("sheet1").UsedRange = inarr
 
End Sub

 

[fbohlandt]

Thanks, that is what I was looking for.