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 "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 :
Technology :
Last Reviewed: March 12, 1999 |
