XL: How to Determine Top/Bottom Used Cells in a Sparse Array

ID: Q142526

The information in this article applies to:

Microsoft Excel 97 for Windows
Microsoft Excel for Windows 95, versions 7.0, 7.0a
Microsoft Excel for Windows, versions 5.0, 5.0c
Microsoft Excel 98 Macintosh Edition
Microsoft Excel for the Macintosh, versions 5.0, 5.0a

SUMMARY

This article discusses how you can determine the beginning and end used rows of a range by using the IF, ISBLANK, MAX, MIN, and ROW functions. The results can provide information to other functions and can help you to determine variable ranges. This article provides formulas for finding the first and last rows, plus an example of how you can select a variable range using these formulas. The following example assumes that there is at least one used cell. Complications caused by empty ranges are beyond the scope of this article.

MORE INFORMATION

First Row

The formula to return the row number of the first used cell in a range is as follows:


   =MIN(IF(ISBLANK(A2:A400),17000,ROW(A2:A400)))

As an alternative, to return the row of the first number, the formula is as follows:


   =MIN(IF(ISNUMBER(A2:A400),ROW(A2:A400),17000))

Last Row

The formula to return the row number of the last used cell in a range is as follows:


   =MAX(IF(ISBLANK(A2:A400),0,ROW(A2:A400)))

As an alternative, to return the row of the last number, the formula is as follows:


   =MAX(IF(ISNUMBER(A2:A400),ROW(A2:A400),0))

How to Use the Results of These Formulas

Because the answers returned by the formulas are the rows meeting the IF condition statements, you can select the range from the top numerical cell to the bottom numerical cell, excluding cells above and below that used range. Note that unused cells can be inside the range and will not be excluded by the example. The example looks for a number, therefore you need to use the alternate formulas (that is, the formulas to find the row of the first number and the row of the last number).

To determine the range, type all of the following into a single cell outside 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 used only to fit the formula into this document. Do not include any spaces as you type it into the cell.

All of the above are array formulas and may require the Array entry. If you are using Microsoft Windows, press CTRL+SHIFT+ENTER. If you are using a Macintosh computer, press COMMAND+RETURN.

If you use an array range in excess of 2,730 cells, you may receive an error 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, please see the following article in the Microsoft Knowledge Base:

Q137921 : Error If Array Contains More Than 2730 Elements

REFERENCES

For more information about Arrays in Microsoft Excel 7.0, click Answer Wizard on the Help menu, and type:


   What is an array?

Additional query words: 8.00 97 XL97 98 XL98

Keywords : xlformula
Version : WINDOWS:5.0,7.0,97; MACINTOSH:5.0,5.0a,98
Platform : MACINTOSH WINDOWS
Issue type : kbhowto kbinfo