Results 1 to 6 of 6

Find First Visible Filtered Row (Sub vs. Function)

This is a discussion on Find First Visible Filtered Row (Sub vs. Function) within the Excel Questions forums, part of the Question Forums category; Greetings, Here's my goal: Pull the value of the first visible cell in Col B (visible after a filter has ...

  1. #1
    New Member
    Join Date
    Apr 2011
    Location
    Virginia
    Posts
    2

    Default Find First Visible Filtered Row (Sub vs. Function)

    Greetings,

    Here's my goal: Pull the value of the first visible cell in Col B (visible after a filter has been applied), to use as a title/legend/label on a chart which updates itself as the filter is changed.

    My preference is to create a user defined function, rather than a Sub macro, as the UDF will automatically update itself, where I would have to capture some event to trigger the VBA. I’m using Excel 2003.

    So I tried both ways with unexpected results. In my example, the first visible row is 4 (excluding the header) after filtering Col B on “Large”.

    1 SIZE
    2 Small
    3 Medium
    4 Large

    Code:
    Sub FirstVisibleRow()
      FirstVisibleRowNumber = ActiveSheet.AutoFilter.Range.Offset(1, 0).SpecialCells(xlCellTypeVisible).Cells(1, 2).Row
    End Sub

    The subroutine returns the value FirstVisibleRowNumber = 4, as desired.


    Code:
    Function First_Visible_Row()
      First_Visible_Row = ActiveSheet.AutoFilter.Range.Offset(1, 0).SpecialCells(xlCellTypeVisible).Cells(1, 2).Row
    End Function 

    The function returns the value First_Visible_Row = 2, which is its “apparent” row number when filtered, but I want to be able to pull the contents of Cells(First_Visible_Row, 2) -- i.e. Row 4. Surprisingly, as I step through the code in the Function, the watched value of First_Visible_Row is actually 4, until the End Function line is executed, and my cell containing the UDF reports a value of 2.

    I even tried having the UDF call the Sub, with the same result.

    I expect I’m missing some pretty fundamental behavior of UDFs. Thanks in advance for the help.

    May God bless you all this Easter.

    Keifffer

  2. #2
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,133

    Default Re: Find First Visible Filtered Row (Sub vs. Function)

    Quote Originally Posted by Keifffer View Post
    Greetings,

    Here's my goal: Pull the value of the first visible cell in Col B (visible after a filter has been applied), to use as a title/legend/label on a chart which updates itself as the filter is changed.

    My preference is to create a user defined function, rather than a Sub macro, as the UDF will automatically update itself, where I would have to capture some event to trigger the VBA. I’m using Excel 2003.

    So I tried both ways with unexpected results. In my example, the first visible row is 4 (excluding the header) after filtering Col B on “Large”.

    1 SIZE
    2 Small
    3 Medium
    4 Large

    Code:
    Sub FirstVisibleRow()
     FirstVisibleRowNumber = ActiveSheet.AutoFilter.Range.Offset(1, 0).SpecialCells(xlCellTypeVisible).Cells(1, 2).Row
    End Sub

    The subroutine returns the value FirstVisibleRowNumber = 4, as desired.


    Code:
    Function First_Visible_Row()
     First_Visible_Row = ActiveSheet.AutoFilter.Range.Offset(1, 0).SpecialCells(xlCellTypeVisible).Cells(1, 2).Row
    End Function 

    The function returns the value First_Visible_Row = 2, which is its “apparent” row number when filtered, but I want to be able to pull the contents of Cells(First_Visible_Row, 2) -- i.e. Row 4. Surprisingly, as I step through the code in the Function, the watched value of First_Visible_Row is actually 4, until the End Function line is executed, and my cell containing the UDF reports a value of 2.

    I even tried having the UDF call the Sub, with the same result.

    I expect I’m missing some pretty fundamental behavior of UDFs. Thanks in advance for the help.

    May God bless you all this Easter.

    Keifffer
    You can do this with a regular worksheet formula. Interested in that?
    .
    Biff
    Microsoft MVP - Excel

    Using Excel 2002, 2007
    KISS - Keep It Simple Stupid

  3. #3

    Join Date
    Feb 2003
    Location
    Gurgaon/Thrissur
    Posts
    2,615

    Default Re: Find First Visible Filtered Row (Sub vs. Function)

    Hi,

    This formula would give you the desired result.

    =INDEX(B2:B100,MATCH(1,SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW($B$2),,1)),0))

    Confirmed with CTRL + SHIFT + ENTER

    HTH

  4. #4

    Join Date
    Feb 2003
    Location
    Gurgaon/Thrissur
    Posts
    2,615

    Default Re: Find First Visible Filtered Row (Sub vs. Function)

    If you prefer an UDF, may be..

    Code:
    Function FirstVisibleValue(ByRef Sht As Worksheet, ByVal FilterCol As Long)
        Dim r As Range
        
        If Sht.AutoFilterMode Then
            Set r = Sht.AutoFilter.Range
            FirstVisibleRow = r.Offset(1, FilterCol - 1).Resize(r.Rows.Count, 1).SpecialCells(12).Cells(1)
        End If
        
    End Function
    and call

    Code:
    MsgBox FirstVisibleValue(ActiveSheet, 2)
    HTH

  5. #5
    New Member
    Join Date
    Apr 2011
    Location
    Virginia
    Posts
    2

    Default Re: Find First Visible Filtered Row (Sub vs. Function)

    Kris,

    1. Formula using INDEX: Very elegant--thanks! I had originally tried something similar, but my sheet has about 50,000 rows, and so it slowed to an unusable crawl. This is very slick.

    2. [Minor typo in your UDF: "FirstVisibleRow = " should read "FirstVisibleValue = "]

    Your example re-highlights my original question. When I call your UDF, it gives the correct value. However, if I insert the UDF into Cell G1, it incorrectly returns the value in row 2 (which is filtered invisible).

    So the question is from my original post is, why does the line...

    Code:
     
    FirstVisibleRowNumber = ActiveSheet.AutoFilter.Range.Offset(1, 0).SpecialCells(xlCellTypeVisible).Cells(1, 2).Row
    ...within a subroutine work, but the line...

    Code:
    First_Visible_Row = ActiveSheet.AutoFilter.Range.Offset(1, 0).SpecialCells(xlCellTypeVisible).Cells(1, 2).Row
    ...within a function, inserted into a cell, produce an inaccurate result? I'm stumped!

    Biff: If your regular worksheet formula isn't the same as Kris', then yes I'm interested.

    Thanks to all,

    Keifffer

  6. #6
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,133

    Default Re: Find First Visible Filtered Row (Sub vs. Function)

    Biff: If your regular worksheet formula isn't the same as Kris', then yes I'm interested.
    It's the same.
    .
    Biff
    Microsoft MVP - Excel

    Using Excel 2002, 2007
    KISS - Keep It Simple Stupid

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com