xlCellTypeVisible not returning filtered range, instead whole range

Question

okay folks, totally baffled here...

I have a linked table in Excel from Access. I am trying to write a vba function that returns the filtered range address of a given column of that table. Bear in mind that I am trying to stick with structured references (for example, Table1[[#Data],[Column2]] ) since it's a linked table and is designed to refresh and change over time.

I am using xlCellTypeVisible with no avail. The function still returns the whole range, even though it is filtered.

Further confusing is I created a nearly identical Sub (instead of Function so I can step through) which is correctly returning the desired return! I stumped; I just can't duplicate it in the Function. I suspect it has something to do with the structured references.

The function "filteredRange" incorrectly returns the whole range "$F$2:$F74" when I enter this into any cell in Excel.

=filteredRange(Table_RyanDB[[#Data],[LC]])

Whereas the following Sub "test" does returns the correct answer "$F$2:$F$14". I can't seem to discern why they're not outputting the same with the input variable is identical.

Sub test()
    Dim theRange As Range
    Set theRange = Range("Table_RyanDB[[#Data],[LC]]")
    MsgBox theRange.Rows.SpecialCells(xlCellTypeVisible).Address
End Sub



Function filteredRange(theRange As Range)
    filteredRange = theRange.SpecialCells(xlCellTypeVisible).Address
End Function

Answer 1

Excel UDF has some limitations and SpecialCells(xlCellTypeVisible) not working propertly here. Use this one instead:

Function filteredRange(theRange As Range)
    Dim rng As Range
    Dim r As Range

    For Each r In theRange.Rows
        If Not r.Hidden Then
            If rng Is Nothing Then
                Set rng = r
            Else
                Set rng = Union(rng, r)
            End If
        End If
    Next
    If Not rng Is Nothing Then filteredRange = rng.Address
End Function