Deleting the number 0 from entire sheet

Roger Govier · #3 05-24-2018, 05:57 AM

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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to map number of sheets one line per sheet into one sheet	abbani	Excel	3	12-12-2016 04:10 AM
Reuse slides deleting slide number and footer	PSSMargaret	PowerPoint	0	05-12-2016 05:28 AM
save sum of 2 number after deleting them	tesoke	Excel	3	12-17-2015 02:14 PM
Excel sheet Sequence Number	belloffice	Excel	1	02-25-2010 02:41 PM
Enter Number on any sheet one time only.	paulrm906	Excel	1	04-28-2006 07:35 AM