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.