Find First Visible Filtered Row (Sub vs. Function)

[Keifffer]

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.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
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.
<o></o>
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”.
<o></o>
1 SIZE<o></o>
2 Small<o></o>
3 Medium<o></o>
4 Large
<o></o>

[FONT=Arial]Sub FirstVisibleRow()<o:p></o:p>[/FONT]
[FONT=Arial]  FirstVisibleRowNumber = ActiveSheet.AutoFilter.Range.Offset(1, 0).SpecialCells(xlCellTypeVisible).Cells(1, 2).Row<o:p></o:p>[/FONT]
[FONT=Arial]End Sub[/FONT]

<o></o>
The subroutine returns the value FirstVisibleRowNumber = 4, as desired.
<o></o>
<o></o>

[FONT=Arial]Function First_Visible_Row()<o:p></o:p>[/FONT]
[FONT=Arial]  First_Visible_Row = ActiveSheet.AutoFilter.Range.Offset(1, 0).SpecialCells(xlCellTypeVisible).Cells(1, 2).Row<o:p></o:p>[/FONT]
[FONT=Arial]End Function [/FONT]

<o></o>
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.
<o></o>
I even tried having the UDF call the Sub, with the same result.
<o></o>
I expect I’m missing some pretty fundamental behavior of UDFs. Thanks in advance for the help.
<o></o>
May God bless you all this Easter.
<o></o>
Keifffer

 

[T. Valko]

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.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
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.
<o></o>
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”.
<o></o>
1 SIZE<o></o>
2 Small<o></o>
3 Medium<o></o>
4 Large
<o></o>

[FONT=Arial]Sub FirstVisibleRow()<o:p></o:p>[/FONT]
[FONT=Arial] FirstVisibleRowNumber = ActiveSheet.AutoFilter.Range.Offset(1, 0).SpecialCells(xlCellTypeVisible).Cells(1, 2).Row<o:p></o:p>[/FONT]
[FONT=Arial]End Sub[/FONT]

<o></o>
The subroutine returns the value FirstVisibleRowNumber = 4, as desired.
<o></o>
<o></o>

[FONT=Arial]Function First_Visible_Row()<o:p></o:p>[/FONT]
[FONT=Arial] First_Visible_Row = ActiveSheet.AutoFilter.Range.Offset(1, 0).SpecialCells(xlCellTypeVisible).Cells(1, 2).Row<o:p></o:p>[/FONT]
[FONT=Arial]End Function [/FONT]

<o></o>
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.
<o></o>
I even tried having the UDF call the Sub, with the same result.
<o></o>
I expect I’m missing some pretty fundamental behavior of UDFs. Thanks in advance for the help.
<o></o>
May God bless you all this Easter.
<o></o>
Keifffer

You can do this with a regular worksheet formula. Interested in that?

 

[Krishnakumar]

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

 

[Krishnakumar]

If you prefer an UDF, may be..

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

MsgBox FirstVisibleValue(ActiveSheet, 2)

HTH

 

[Keifffer]

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...

FirstVisibleRowNumber = ActiveSheet.AutoFilter.Range.Offset(1, 0).SpecialCells(xlCellTypeVisible).Cells(1, 2).Row

...within a subroutine work, but the line...

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

 

[T. Valko]

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

It's the same.