Finding End of Rows and Columns in Excel Using XLM CodeLast reviewed: August 10, 1995Article ID: Q41489 |
The information in this article applies to:
SUMMARYTo 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 INFORMATIONThe 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
© 1998 Microsoft Corporation. All rights reserved. Terms of Use. |