Morning All,
I would like to remove the leading apostrophes from numbers stored as text in column A and preserve the leading zeros by formatting the value. The below procedure works if I run it twice but after the first run all it seems to do is to cause Excel to show the green tick mark for the cells error message and I’m not sure why?
Thanks in advance for any help!
I would like to remove the leading apostrophes from numbers stored as text in column A and preserve the leading zeros by formatting the value. The below procedure works if I run it twice but after the first run all it seems to do is to cause Excel to show the green tick mark for the cells error message and I’m not sure why?
Thanks in advance for any help!
Code:
Sub DeathToApostrophe()
Dim s As Range
Application.ScreenUpdating = False
For Each s In ActiveSheet.Range("A2:A100")
If s.HasFormula = False Then
'Gets text and rewrites to same cell without the apostrophe.
s.Value = Format(s.Value, "000000")
End If
Next s
Application.ScreenUpdating = True
End Sub