Finding End of Rows and Columns in Excel Using XLM Code

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()