Looping through a selected range efficiently

A common task is to create a macro that evaluates each cell in a range and performs an operation if the cell meets a certain criterion. Listing 11-1 provides an example of such a macro. In this example, the SelectiveColorl procedure applies a red background to all cells in the selection that have a negative value. The background of other cells is reset.

Listing 11-1: Coloring All Negative Cells' Backgrounds Red

Sub SelectiveColor1()

' Makes cell background red if the value is negative Dim cell As Range

If TypeName(Selection) <> "Range" Then Exit Sub Const REDINDEX = 3

Application.ScreenUpdating = False For Each cell In Selection If cell.Value < 0 Then cell.Interior.Colorlndex = REDINDEX Else cell.Interior.ColorIndex = xlNone End If Next cell End Sub

The SelectiveColor1 procedure certainly works, but it has a serious flaw. For example, what if the selection consists of an entire column? Or ten columns? Or the entire worksheet? The user would probably give up before all the cells were evaluated. A better solution (SelectiveColor2) is shown in Listing 11-2.

Listing 11-2: Improving This Procedure to Include Wider, Multiple-column Ranges

Sub SelectiveColor2()

' Makes cell background red if the value is negative

Dim cell As Range Dim FormulaCells As Range Dim ConstantCells As Range Const REDINDEX = 3

Ignore errors On Error Resume Next

Application.ScreenUpdating = False Create subsets of original selection

Set FormulaCells = Selection.SpecialCells (xlFormulas, xlNumbers) Set ConstantCells = Selection.SpecialCells (xlConstants, xlNumbers) On Error GoTo 0

Process the formula cells If Not FormulaCells Is Nothing Then For Each cell In FormulaCells If cell.Value < 0 Then cell.Interior.ColorIndex = REDINDEX Else cell.Interior.ColorIndex = xlNone End If Next cell End If

Continued

Listing 11-2 (Continued)

' Process the constant cells

If Not ConstantCells Is Nothing Then For Each cell In ConstantCells If cell.Value < 0 Then cell.Interior.ColorIndex = REDINDEX Else cell.Interior.ColorIndex = xlNone End If Next cell End If End Sub

This procedure performs some extra steps that make it very efficient. I use the SpecialCells method to generate two subsets of the selection: One subset includes only the cells with numeric constants; the other subset includes only the cells with numeric formulas. Then I process the cells in these subsets by using two For Each-Next constructs. The net effect: Only nonblank cells are evaluated, thus speeding up the macro considerably.

The On Error statement is necessary because the SpecialCells method generates an error if no cells qualify. This statement also handles situations in which a range is not selected when the procedure is executed.

0 0

Post a comment