View Single Post
Old 05-24-2018, 05:57 AM
Roger Govier Roger Govier is offline Windows 10 Office 2016
Join Date: Oct 2017
Location: Abergavenny, Wales, UK
Posts: 13
Roger Govier is on a distinguished road


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.

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)
                    impary(r, c) = "'" & Left(impary(r, c), 3) & Mid(impary(r, c), 5, 5)
                End If
    ActiveSheet.Range("C4").Resize(r - 1, c - 1) = impary
    End With
    Application.ScreenUpdating = True
End Sub
Reply With Quote