Microsoft KB Archive/109172

{| = Excel: Select Special, Find, and Replace Commands Ignore Cells =
 * width="100%"|

Last reviewed: September 12, 1996

Article ID: Q109172 The information in this article applies to:
 * 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 An additional solution is to extend the last cell of the worksheet out to the range that you want to search.