Full Version: Capture First & Last Values in a Filtered Column ?
JoeOxfordCT
Hi Excel Experts,

I am trying to capture the first & last values in a filtered column. I can't use the Subtotal function with the Min & Max parameters...I need first & last values.

I know I could put together some clunky code to do this but I also know that someone probably has a niftey formula that will do this much easier...

Any takers ???
Thanks !!!

Joe
Oxford, CT. USA.
KingMartin

(I hate to post loop but SpecialCells(12) shows some unexpected behavior when dealing with autofiltered range)

CODE
[color="blue"]Function[/color] First(col [color="blue"]As[/color] [color="blue"]String[/color])
[color="blue"]Dim[/color] rg [color="blue"]As[/color] Range, cl [color="blue"]As[/color] Range
[color="blue"]Set[/color] rg = Range(Cells(2, col), Cells(65536, col).End(3))
[color="blue"]For[/color] [color="blue"]Each[/color] cl [color="blue"]In[/color] rg
[color="blue"]If[/color] [color="blue"]Not[/color] cl.EntireRow.Hidden [color="blue"]Then[/color]
First = cl.Value
[color="blue"]Exit[/color] [color="blue"]For[/color]
[color="blue"]End[/color] [color="blue"]If[/color]
[color="blue"]Next[/color] cl
[color="blue"]End[/color] [color="blue"]Function[/color]

[color="blue"]Function[/color] Last(col [color="blue"]As[/color] [color="blue"]String[/color])
Last = Cells(65536, col).End(3).Value
[color="blue"]End[/color] [color="blue"]Function[/color]

Use like =FIRST("B") and =LAST("B")

to find the first and last visible cell in column B...

Martin