Microsoft KB Archive/41489

{|
 * width="100%"|

Finding End of Rows and Columns in Excel Using XLM Code

 * }

-

The information in this article applies to:


 * Microsoft Excel for Windows, version 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 &quot;start&quot;, 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) &quot;stop&quot;, and selects the range &quot;start:stop.&quot; (Note that the names &quot;start&quot; and &quot;stop&quot; are arbitrary. You can use any name that denotes the upper left corner and the lower right corner.)

Macro                     Comments -

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

Additional query words:

Keywords :

Version :

Platform :

Issue type :

Technology :