Results 1 to 6 of 6

Thread: Visible range from UDF having filtered Listobject columns as argument

  1. #1
    Junior Member
    Join Date
    May 2012
    Posts
    25
    Rep Power
    0

    Visible range from UDF having filtered Listobject columns as argument

    hi,

    i am filtering a listobject and passing some of its columns to a UDF called from a cell. the function is supposed to return only the filtered visible cells from those listcolumns. However it returns the whole range.

    i have posted the details here:

    excel - selecting SpecialCells(xlCellTypeVisible) from specific columns of ListObject table - Stack Overflow

    can anyone help urgently?

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    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)
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Junior Member
    Join Date
    May 2012
    Posts
    25
    Rep Power
    0
    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.

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Not strange to me BTW, what's your aim to bring the range in the cell ?
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  5. #5
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by Junoon View Post
    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.

  6. #6
    Junior Member
    Join Date
    May 2012
    Posts
    25
    Rep Power
    0
    [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(rngr)
                
    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(rngr)
                    
    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(rngr)
                
    End If
            
    End If
        
    Next
        
    If Not rng Is Nothing Then filteredRange rng     //do something with filteredRange
        

    End Function 

    Cheers mate.

Similar Threads

  1. Replies: 5
    Last Post: 07-30-2014, 07:51 PM
  2. Q: VBA - using a range of strings as an argument
    By mwdking in forum Excel Help
    Replies: 1
    Last Post: 11-11-2013, 10:45 PM
  3. Forcing an Argument to be Passed by Value
    By Transformer in forum Tips, Tricks & Downloads (No Questions)
    Replies: 4
    Last Post: 05-21-2013, 11:28 AM
  4. Worksheet Protection Method with UserInterfaceOnly Argument
    By technicalupload in forum Download Center
    Replies: 1
    Last Post: 09-02-2011, 04:17 PM
  5. Unique Count on a Filtered Range
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 08-14-2011, 04:29 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •