Modifying spreadsheet data with VBA – the quick way

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.

This entry was posted in Arrays, Excel, VBA and tagged , , , . Bookmark the permalink.

One Response to Modifying spreadsheet data with VBA – the quick way

  1. lhm says:

    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, "", xlWhole
    Sheets(Array("Sheet1", "Sheet2")).Select
    Selection.Value2 = 0

    (Note: prior to Excel 2010, there is a 8192 area limit for SpecialCells.)


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s