XlCellTypeVisible does not return the filtered range, but the entire range

Question

Calm people, completely confused here ...

I have a linked table in Excel from Access. I am trying to write a vba function that returns the address of a filtered range for a given column of this table. Keep in mind that I am trying to stick to structured references (for example, Table1 [[# Data], [Column2]]), since this is a related table and is intended to be updated and changed over time.

I am using xlCellTypeVisible to no avail. The function still returns the entire range, even if it is filtered.

Further embarrassed, I created an almost identical Sub (instead of Function so that I could go through), which correctly returns the desired result! I flinched; I just can't duplicate it in a function. I suspect this has something to do with structured links.

The "filterRange" function incorrectly returns the entire range of "$ F $ 2: $ F74" when I enter it in any cell in Excel.

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

While the next "test" Sub returns the correct answer "$ F $ 2: $ F $ 14". I cannot understand why they do not output the same with the input variable.

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)does not work here. Use this 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