Hi
You can do as Macropod suggests and convert all to text, but it need not be a slow process.
Writing to individual cells on the sheet in an iterative process is exceptionally slow as you have the overhead of crossing between VBA and Excel thousands of times and Reading and Writing thousands of times..
If you read all the data into an array with a single Read, process the array and then write that back as a single Write, it is almost instantaneous.
Code:
Sub RemoveZero()
Application.ScreenUpdating = False
Dim r As Long, c As Long
Dim impary
With ActiveSheet
impary = ActiveSheet.UsedRange
For r = 2 To UBound(impary, 1)
For c = 1 To UBound(impary, 2)
If Len(impary(r, c)) = 8 Then
impary(r, c) = "'" & impary(r, c)
Else
impary(r, c) = "'" & Left(impary(r, c), 3) & Mid(impary(r, c), 5, 5)
End If
Next
Next
ActiveSheet.Range("C4").Resize(r - 1, c - 1) = impary
End With
Application.ScreenUpdating = True
End Sub