You can do this with a regular worksheet formula. Interested in that?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”.
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.