-
Visible range from UDF having filtered Listobject columns as argument
-
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)
-
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.
-
Not strange to me :) BTW, what's your aim to bring the range in the cell ?
-
Quote:
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.
-
[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
Cheers mate. :)
