Finding End of Rows and Columns in Excel Using XLM Code

Last reviewed: August 10, 1995
Article ID: Q41489

The information in this article applies to:

  • Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.0

SUMMARY

To select a range of information that contains a variable range of data, you can write a Microsoft Excel macro to select from the starting cell to the end of data, or until a blank cell is found.

MORE INFORMATION

The following macro sets the first cell (the upper left corner) to "start", searches across the row until it finds a blank cell, backs up one cell, searches down the column until it finds a blank cell, backs up one cell, names that cell (the lower right corner) "stop", and selects the range "start:stop." (Note that the names "start" and "stop" are arbitrary. You can use any name that denotes the upper left corner and the lower right corner.)

   Macro                      Comments
   -----                      --------

   =DEFINE.NAME("start")      Names the upper left cell
   =WHILE(ACTIVE.CELL()<>"")  Tests for active cell to be blank
   =SELECT("rc[1]")           Selects the next column to right
   =NEXT()                    Loops if not blank
   =SELECT("rc[-1]")          If blank, backs up one cell
   =WHILE(ACTIVE.CELL()<>"")  Tests for active cell to be blank
   =SELECT("r[1]c")           Selects the next row down
   =NEXT()                    Loops if not blank
   =SELECT("r[-1]c")          If blank, backs up one cell
   =DEFINE.NAME("stop")       Names the cell "stop"
   =SELECT("start:stop")      Selects range
   =RETURN()


KBCategory: kbusage
KBSubcategory:

Additional words: 2.00 2.01 2.10 2.20 2.21 3.00 4.00 5.00


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: August 10, 1995
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.