Microsoft KB Archive/109172

Excel: Select Special, Find, and Replace Commands Ignore Cells 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. REFERENCES "User's Guide 1," version 4.0, page 202-204 "User's Guide," version 3.0, page 169-172

KBCategory: kbusage KBSubcategory: Additional reference words: 2.10 3.00 4.00 4.00a Last reviewed: September 12, 1996 © 1998 Microsoft Corporation. All rights reserved. Terms of Use.