Visible range from UDF having filtered Listobject columns as argument

**Junoon** · 03-24-2016, 04:50 PM

**Admin** · 03-24-2016, 06:51 PM

Code:

    Dim r   As Range
    
    With ActiveSheet.ListObjects(1)
        Set r = Intersect(.DataBodyRange, .DataBodyRange.Offset(, 1)).SpecialCells(12)
    End With
    
    MsgBox r.Address

would return whole bodyrange except the first column (visible cells)

**Junoon** · 03-24-2016, 08:18 PM

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.

PHP Code:


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

PHP Code:


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.

PHP Code:


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.

**Admin** · 03-24-2016, 09:43 PM

Not strange to me

BTW, what's your aim to bring the range in the cell ?

**Rick Rothstein** · 03-25-2016, 03:13 AM

Originally Posted by Junoon

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.

PHP Code:


    Set r = Intersect(.DataBodyRange, .DataBodyRange.Offset(, 1)).SpecialCells(12)

SpecialCells does not work when called from a UDF.

**Junoon** · 03-25-2016, 04:28 AM

[PHP]yes, i agree.

this works if i call it from a cell like this:

PHP Code:


=filteredRange(tab_data[[Brands]:[Index]])

PHP Code:


Function filteredRange(theRange As Range) As String
    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

as it is returning a String.

this works if i am returning a Count:

PHP Code:


Function filteredRange(theRange As Range) As Long
    Dim rng As Range
    Dim r As Range
    Dim Cnt As Long
    
    For Each r In theRange.Rows
        If Not r.Hidden Then
            If rng Is Nothing Then
                Set rng = r
                Cnt = 1
            Else
                Set rng = Union(rng, r)
                Cnt = Cnt + 1
            End If
        End If
    Next
    If Not rng Is Nothing Then filteredRange = Cnt  //do something with filteredRange
    
End Function

This works if function doesnt return a Range type but does find the visible range inside it:

PHP Code:


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     //do something with filteredRange
    

End Function