A question recently came up (here) asking how to speed up the process of scanning a large range of data on one sheet, and wherever a cell had a value of zero, deleting the data in the same cell address on another sheet. A macro selecting cells one at a time was taking up to 7 minutes for this task in Excel 2007.
The secret to completing tasks of this type as quickly as possible is to transfer all the data into VBA arrays in as few operations as possible (two in this case), modify the array from the second sheet enirely in VBA, then write the modified array back to the worksheet in one operation. In this case a 7 minute task was reduced to less than half a second, about 1000 times faster!
The “quick and dirty” code that did the job is shown below:
Sub DeleteZeros()
Dim Sheet1Vals As Variant, Sheet2Vals As Variant, i As Long, j As Long
Sheet1Vals = Sheets("Sheet1").Range("A2:UN901").Value2
Sheet2Vals = Sheets("Sheet2").Range("A2:UN901").Value2
For i = 1 To 900
For j = 1 To 560
If Sheet1Vals(i, j) = 0 Then Sheet2Vals(i, j) = ""
Next j
Next i
Sheets("Sheet2").Range("A2:UN901") = Sheet2Vals
End Sub
Note that using the “.Value2” property of the ranges, rather than “.Value”, speeds up the operation by about 50%. Not really important in this case, but in other situations it can be.
Newton Excel Bach, not (just) an Excel Blog 
Quick and neat approach and a huge improvement on the original!
Another alternative which is probably a little less quick but easy to implement:
1. Replace the zero values on sheet1 with a blank (or text/boolean/error)
2. Ctrl+Click Sheet2 and choose goto…special blanks (or text/boolean/error)
3. With this selection, enter zero’s on Sheet1 and clear cells on sheet2
You can use undo/redo to backtrack or repeat steps, vba equivalent code is:
Cells.Replace 0, "", xlWholeSheets(Array("Sheet1", "Sheet2")).Select
Cells.SpecialCells(xlCellTypeBlanks).Select
Sheets("Sheet1").Select
Selection.Value2 = 0
Sheets("Sheet2").Select
Selection.ClearContents
(Note: prior to Excel 2010, there is a 8192 area limit for SpecialCells.)
LikeLike