Remove apostrophe per KB124935

[Steve__]

Microsoft Knowledge Base Article - 124935.

This question is somewhat linked to another question I
just posted. When I run the macro from the above KB
Article to remove the leading hidden apostrophe in cells,
if I select the whole worksheet before running the macro,
this macro takes forever to calculate. I'm guessing
that's because it's trying all 66,536 x 256 cells. I
guess that because if I select a cell, it's instant, or if
I select a whole column, it takes about 30 seconds (66,536
cells), if I select 4 columns, it takes about 120
seconds.

Is there any way to have this macro select only the range
in the worksheet that has data? For reference, here is
the macro in question:

The following procedure removes the apostrophe in front of
text, values, or formulas in the current selection:

'Removes hidden apostrophes that are first characters.
'Works on cells with formulas, text, or values.

Sub ApostroRemove()
For Each currentcell In Selection
If currentcell.HasFormula = False Then
'Verifies that procedure does not change the
'cell with the active formula so that it
contains
'only the value.
currentcell.Formula = currentcell.Value
End If
Next
End Sub

 

[Chip Pearson]

Steve,

Change

For Each currentcell In Selection
to
For Each currentcell In
ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)

 

[J.E. McGimpsey]

one way:

Public Sub ApostroRemove()
Dim currentCell As Range
On Error Resume Next
For Each currentCell In Selection.SpecialCells( _
xlCellTypeConstants, xlTextValues)
With currentCell
.Formula = .Value
End With
Next
On Error GoTo 0
End Sub