UtterAccess Discussion Forums > Capture First & Last Values in a Filtered Column ?

Full Version: Capture First & Last Values in a Filtered Column ?

UtterAccess Discussion Forums > Microsoft® Office > Microsoft Excel

JoeOxfordCT

Mar 21 2006, 12:32 PM

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

Mar 21 2006, 06:07 PM

This may be answered in your other thread, but you can try the following:

(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

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.