XL: How to Determine Top/Bottom Used Cells in a Sparse Array
This article was previously published under Q142526
This article has been archived. It is offered "as is" and will no longer be updated.
This article discusses how you can determine the beginning and end usedrows of a range by using the IF, ISBLANK, MAX, MIN, and ROW functions.The results can provide information to other functions and can help youto determine variable ranges. This article provides formulas for findingthe first and last rows, plus an example of how you can select a variablerange using these formulas. The following example assumes that there isat least one used cell. Complications caused by empty ranges are beyondthe scope of this article.
First RowThe formula to return the row number of the first used cell in a range isas follows:
=MIN(IF(ISBLANK(A2:A400),17000,ROW(A2:A400)))As an alternative, to return the row of the first number, the formula isas follows:
Last RowThe formula to return the row number of the last used cell in a range isas follows:
=MAX(IF(ISBLANK(A2:A400),0,ROW(A2:A400)))As an alternative, to return the row of the last number, the formula isas follows:
How to Use the Results of These FormulasBecause the answers returned by the formulas are the rows meeting the IFcondition statements, you can select the range from the top numericalcell to the bottom numerical cell, excluding cells above and below thatused range. Note that unused cells can be inside the range and will notbe excluded by the example. The example looks for a number, therefore youneed to use the alternate formulas (that is, the formulas to find the rowof the first number and the row of the last number).
To determine the range, type all of the following into a single celloutside the range A2:A400:
=SUM(OFFSET(A1,MIN(IF(ISNUMBER(A2:A400),ROW(A2:A400),17000)),0, MAX(IF(ISNUMBER(A2:A400),ROW(A2:A400),0))- MIN(IF(ISNUMBER(A2:A400),ROW(A2:A400),17000))+1,1))NOTE: There are no spaces in the above formula; multiple lines are usedonly to fit the formula into this document. Do not include any spaces asyou type it into the cell.
All of the above are array formulas and may require the Array entry. Ifyou are using Microsoft Windows, press CTRL+SHIFT+ENTER. If you are usinga Macintosh computer, press COMMAND+RETURN.
If you use an array range in excess of 2,730 cells, you may receive anerror because array management is limited to one segment of memory (64K).Some versions of Microsoft Excel may be able to use larger arrays,depending on the internal length the code uses for number storage.(Programs using 16-bit code can use larger arrays than programs using 32-bit code.) This is by design in Microsoft Excel.
For additional information about this behavior in Microsoft Excel, click the article number below to view the article in the Microsoft Knowledge Base:
137921 XL: Error If Array Contains More Than 2730 Elements
Excel 97 and LaterFor more information about arrays, click Microsoft Excel Help on the Help menu, type what is an array in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.
Excel 7.0For more information about arrays in Microsoft Excel 7.0, click AnswerWizard on the Help menu, and type:
What is an array?
8.00 97 XL97 98 XL98 XL2001
Article ID: 142526 - Last Review: 10/26/2013 11:32:00 - Revision: 2.0
Microsoft Excel 97 Standard Edition, Microsoft Excel 95 Standard Edition, Microsoft Excel 5.0 Standard Edition, Microsoft Excel 2001 for Mac, Microsoft Excel 98 for Macintosh, Microsoft Excel 5.0 for Macintosh
- kbnosurvey kbarchive kbhowto kbinfo KB142526