Find last row in range using INDEX

**skysurfer** · 09-03-2010, 01:51 PM

Hi,

How do I modify the following array formula to read a range that starts in cell C18 and extends down rows and across columns that are numbers referenced in two cells?

Here's the formula now (which returns the last row containing any type of data in a specified range):

={(MAX((C$18:L28<>"")*ROW(C$18:L28)))-ROW(C$18:L28)+1}

What I need is to have L28 replaced by INDEX (I think) that references cell A1 containing the last row number and cell A2 containing the last column number in the range to be searched.

Thanks,

Lawrence

**DonkeyOte** · 09-03-2010, 01:55 PM

Not clear which version you're running but in essence references to L28 become:

INDEX($1:$65536,$A$1,$A$2)

**skysurfer** · 09-03-2010, 02:09 PM

DonkeyOte,

I'm using XL2003 and 2010. Is there issue between these version using this formula, or does that only apply to version prior 2003?

Thanks for the help! Works beautifully.

Lawrence

Originally Posted by DonkeyOte

Not clear which version you're running but in essence references to L28 become:

INDEX($1:$65536,$A$1,$A$2)

**DonkeyOte** · 09-03-2010, 02:23 PM

The number of ROWS varies between the versions (XL2007 onwards has in excess of 1m rows) though if you're unlikely to use greater than 65536 rows it shouldn't be an issue.