Part VI: Automating Excel
through each cell—even those that are blank. What’s needed is a way to process only the
nonblank cells.
You can accomplish this task by using the SpecialCells method. In the following exam-
ple, the SpecialCells method is used to create a new object: the subset of the selection
that consists of cells with constants (as opposed to formulas). This subset is processed,
with the net effect of skipping all blank cells and all formula cells:
Sub ProcessCells2()
Dim ConstantCells As Range
Dim Cell As Range
' Ignore errors
On Error Resume Next
' Process the constants
Set ConstantCells = Selection.SpecialCells(xlConstants,
xlNumbers)
For Each Cell In ConstantCells
If Cell.Value < 0 Then Cell.Value = Cell.Value * -1
Next Cell
End Sub
The ProcessCells2 procedure works fast, regardless of what is selected. For example, you
can select the range, select all columns in the range, select all rows in the range, or even
select the entire worksheet. In all of these cases, only the cells that contain constants are
processed inside the loop. This procedure is a vast improvement over the ProcessCells
procedure presented earlier in this section.
Notice that the following statement is used in the procedure:
On Error Resume Next
This statement causes Excel to ignore any errors that occur and simply process the next
statement. This statement is necessary because the SpecialCells method produces an
error if no cells qualify and because the numerical comparison will fail if a cell contains an
error value. Normal error checking is resumed when the procedure ends. To return to normal
error-checking mode inside a procedure, use the following statement:
On Error GoTo 0
This macro is available on this book’s website at http://www.wiley.com/go/excel2019bible. The file
is named skip blanks while looping.xlsm.
Prompting for a cell value
As discussed in Chapter 44, “Creating UserForms,” you can take advantage of the VBA
InputBox function to ask the user to enter a value. Figure 47.2 shows an example.
