I was graciously helped previously to help find a value in the first row of
an array when changing the autofilter, but now how do I figure out the last
row?

Here's the formula to find the first row.

=INDEX(B15:B48479,MATCH(1,(SUBTOTAL(3,OFFSET(B15:B 48479,ROW(B15:B48479)-MIN(ROW(B15:B48479)),0,1))*(B15:B48479<"")),0))

I want to be able to compare the first and last row and see if they are the
same or not.

Thanks so much for looking at this!

try this idea

Sub firstlast()
Set Rng = Range("a7:h100").SpecialCells(xlCellTypeVisible)
lr = Cells(Rows.Count, 1).End(xlUp).Row
'MsgBox lr
MsgBox Rng.Cells(1, 8)
MsgBox Cells(lr, 8)
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Christa" wrote in message
...
I was graciously helped previously to help find a value in the first row of
an array when changing the autofilter, but now how do I figure out the
last
row?

Here's the formula to find the first row.

=INDEX(B15:B48479,MATCH(1,(SUBTOTAL(3,OFFSET(B15:B 48479,ROW(B15:B48479)-MIN(ROW(B15:B48479)),0,1))*(B15:B48479<"")),0))

I want to be able to compare the first and last row and see if they are
the
same or not.

Thanks so much for looking at this!

Try something like this (array entered):

=INDEX(B15:B48479,MAX((SUBTOTAL(3,OFFSET(B15:B4847 9,ROW(B15:B48479)-MIN(ROW(B15:B48479)),0,1)))*(ROW(B15:B48479)-MIN(ROW(B15:B48479))+1)))

With that large of a range expect this to be slow!

If the range is *permanent* and you will *never* insert new rows above the
range or add new rows to the end of the range you can eliminate a couple of
calculations that will save some resources:

=INDEX(B15:B48479,MAX(SUBTOTAL(3,OFFSET(B15:B48479 ,ROW(B15:B48479)-15,0,1))*ROW(B1:B48465)))

--
Biff
Microsoft Excel MVP


"Christa" wrote in message
...
I was graciously helped previously to help find a value in the first row of
an array when changing the autofilter, but now how do I figure out the
last
row?

Here's the formula to find the first row.

=INDEX(B15:B48479,MATCH(1,(SUBTOTAL(3,OFFSET(B15:B 48479,ROW(B15:B48479)-MIN(ROW(B15:B48479)),0,1))*(B15:B48479<"")),0))

I want to be able to compare the first and last row and see if they are
the
same or not.

Thanks so much for looking at this!