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



THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: September 12, 1996
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.