Visible range from UDF having filtered Listobject columns as argument

[Junoon]

hi,

Strange as it may seem, a UDF function doesn't seem to return the visible range, but if you convert it to a Sub procedure, it will return the visible range.

Sub Subit(tabdata As ListObject)
Dim r   As Range
    
With tabdata
    Set r = Intersect(.DataBodyRange, .DataBodyRange.Offset(, 1)).SpecialCells(12)
End With

MsgBox r.Address
    
End Sub 

whereas a function called from a worksheet cell will return #Value

Function Funcit(tabdata As ListObject) As Range
Dim r   As Range
    
With tabdata
    Set r = Intersect(.DataBodyRange, .DataBodyRange.Offset(, 1)).SpecialCells(12)
End With

MsgBox r.Address
    
End Function 

I can definitely call this function from another procedure passing the listobject to it and it works.

Sub Runit()
    Call Funcit(ActiveSheet.ListObjects(1))
End Sub 

But this doesnt solve my query as i want to pass a listobject range to a UDF in a cell and in turn get its visible range from the function.