get value from last row of an autofilter array

[Christa]

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:B48479,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!

 

[Don Guillett]

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

 

[T. Valko]

Try something like this (array entered):

=INDEX(B15:B48479,MAX((SUBTOTAL(3,OFFSET(B15:B48479,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)))