Routine to get first and the last value from filtered column RRS feed

  • Question

  • Hello,

    Can you help me to create a routine, which finds the first and the last value from auto filtered column?

    I have made a routine that reads text files,- loads data to Spread sheet and filters depending on user request. However, for mathematical calculations I need to use the first and the last value from auto filtered column together with max and min value.

    For max and min value I use Excel function SUBTOTAL.

    Does anybody has an idea how to pick first and the last value from filtered column?

    Thank you!

    Wednesday, September 23, 2015 3:04 PM


  • The code below is a UDF (User Defined Function). For the benefit of anyone who sees this post, the SpecialCells(xltypeVisible) code does not work with UDF's called from a worksheet and hence the use of looping through the range to identify the unhidden cells.

    Enter the one of the formulas below into a cell on the worksheet. First formula to return first visible and second formula to return last visible.

    The Cell Id (A7) is any cell in the column required. Can use the column header cell or any other cell.

    True or False in the formula determines whether it returns the first or last cell.

    =FirstLastVis(A7,TRUE)                    'Returns the first visible data cell value

    =FirstLastVis(A7,FALSE)                   'Returns the last visible data cell value

    Not sure of your expertise so here are some guidelines to install the code.

    1. Alt and F11 to open the VBA editor.
    2. Select Menu item Insert -> Module.
    3. Copy the code below into the VBA editor.
    4. Close the VBA editor (Cross with red background or turns red when cursor hover over it)
    5. Save the workbook as "Macro enabled".
    6. Ensure Macros are enabled. See Help for how to do this. (Option to "Disable all with notification" should be OK.

    Function FirstLastVis(rng As Range, FirstLast As Boolean) As Range
        Dim ws As Worksheet
        Dim rngToTest As Range
        Dim rngCel As Range
        Set ws = Application.Caller.Parent
        With ws.AutoFilter.Range
            Set rngToTest = .Columns(rng.Column).Offset(1, 0) _
                            .Resize(.Rows.Count - 1, 1)
        End With
        For Each rngCel In ws.Range(rngToTest.Address)
            If rngCel.EntireRow.Hidden = False Then
                If FirstLast Then
                    Set FirstLastVis = rngCel
                    Exit For
                    Set FirstLastVis = rngCel
                End If
            End If
        Next rngCel

    End Function

    Regards, OssieMac

    Thursday, September 24, 2015 4:16 AM