fin last row withtin a range using built functions not VBA
And I should have noted that you might want to change the +40 and the +39 in
your formula and in my first formula to
ROW($A$41)-1 or ROW($A$41)-2
to make the formulas properly respond to row insertions or deletions in the
range A1:A40.
Bernie
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Luis,
If the values are contiguous, then the simplest to use and understand
would be
=MATCH(A9,$A$41:$A$10000,0)+COUNTIF($A$40:$A$10000 ,A9)+39
If they are not contiguous, then array enter (enter using
Ctrl-Shift-Enter)
=MAX(($A$40:$A$10000=A9)*ROW($A$40:$A$10000))
HTH,
Bernie
MS Excel MVP
"LuisE" wrote in message
...
I have blocks of data in a sheet, every time there is an entry in column A
a
new range begins and (in terms of rows) goes as far as until there is
another
entry in Column A.
All the entries in Column A are in A9 with "validation data list" i need
to
retireve the last row of the range of the selected entry using built in
formulas.
I have this to get the first row and it works
=MATCH( A9,$A$41:$A$10000,0)+40
Need the one to find the last row of that range which is delimited by the
next entry in ColA
Thanks in advance
|