Finding End of Rows and Columns in Excel Using XLM Code

ID: Q41489

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

Additional query words:

Keywords :
Version :
Platform :
Issue type :