Excel: Select Special, Find, and Replace Commands Ignore Cells

• Microsoft Excel for Windows versions 2.10, 3.0, 4.0, 4.0a

SUMMARY

In Microsoft Excel, the Select Special and the Find and Replace commands on the Formula menu search the cells up to and including the last row or column of the worksheet. The last cell is the same cell specified by the SELECT.LAST.CELL() function.

MORE INFORMATION

If you attempt to use Select Special, Find, or Replace, in a macro to locate blank cells within a given area, only the area up to and including the last cell in a worksheet is used for the search.

To locate blank cells that extend beyond the last cell of the worksheet, create a loop macro that will select the next cell for the duration of whatever condition you need to meet will operate both within the range of the last cell as well as outside that range.

The following example selects the first 24 blank cells, starting at the active cell in the same column, and places the phrase "Was Blank" in those cells.

   A1: =SET.NAME("COUNT",0)
   A2: =WHILE(COUNT<25)
   A3: =IF(ISBLANK(ACTIVE.CELL()))
   A4: =   FORMULA("Was Blank")
   A5: =   SET.NAME("COUNT",COUNT+1)
   A6: =END.IF()
   A7: =SELECT("R[1]C")
   A8: =NEXT()
   A9: =RETURN()

REFERENCES

"User's Guide 1," version 4.0, page 202-204 "User's Guide," version 3.0, page 169-172