Results 1 to 3 of 3

Thread: Placing an Autofiltered range inside an array works only once
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jan 2018
    Posts
    233
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Placing an Autofiltered range inside an array works only once

    I'm currently having a problem where an auto-filtered range is only placed inside an array on the first loop but subsequent attempts to do the same despite the range filtering correctly don't work.
    Code:
    Set OpenWBS = Workbooks(Workbooks.Count).Worksheets(1).UsedRange
                    
                    strData = OpenWBS.Columns(T).Value2 'contract codes column has been added to array[T is a number]
                    strData = No_Repeats(strData, strData(1, 1), UBound(strData, 1)) 'remove duplicates
                                         
                    For Each element In strData
                        
                        Select Case element
                            
                            Case strData(LBound(strData)) 'skip over headers
                            
                            Case Else
                                    
                                OpenWBS.AutoFilter Field:=T, Criteria1:=element, Operator:=xlFilterValues, VisibleDropDown:=false
                                'filtering worksheet for rows with the specified contract code
                                                
                                Temp_Array = OpenWBS.SpecialCells(xlCellTypeVisible).Value2
                            
                                Contract_ID = element
                    
                                If Not ThisWorkbook.All_Contracts.Exists(Contract_ID) Then
            
                                        'if the Contract_ID Key does not exist then create a new dictionary
                                        
                                        Set C_Dict = New Scripting.Dictionary 'create a new dictionary with the Contract_ID as a key
                                        
                                        ThisWorkbook.All_Contracts.Add Contract_ID, C_Dict
            
                                        'add the new dictionary to the orginal
                                End If
            
                                Set C_Dict = ThisWorkbook.All_Contracts.Item(Contract_ID)       'dictionary within a dictionary
                             
                                For X = LBound(Temp_Array, 1) + 1 To UBound(Temp_Array, 1) 'offset by 1 to skip headers in visible range
                                
                                    D = Convert_Date(Temp_Array(X, 2)) 'the 2nd element corresponds with a date string--> convert to serial date
                            
                                    C_Dict.Add D & "-" & Temp_Array(X, 1), WorksheetFunction.Index(Temp_Array, X, 0)
                                    'key is SerialDate-Name
                                Next X
                                
                            OpenWBS.AutoFilter Field:=T 'clear the autofilter from the contract column
                            
                            'Erase Temp_Array
                            
                        End Select
                                   
                    Next element
    Last edited by MoshiM; Jul 18th, 2019 at 01:39 PM.

  2. #2
    Board Regular
    Join Date
    Jan 2018
    Posts
    233
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Placing an Autofiltered range inside an array works only once

    Solved with the solution found here. Sorry for the inconvenience.
    https://www.mrexcel.com/forum/excel-...ter-array.html

  3. #3
    Board Regular
    Join Date
    Jan 2018
    Posts
    233
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Placing an Autofiltered range inside an array works only once

    Quote Originally Posted by MoshiM View Post
    Solved with the solution found here. Sorry for the inconvenience.
    https://www.mrexcel.com/forum/excel-...ter-array.html
    I've found that the above method does not work when the filtered range is non-contigous. I know one method around this is to load the address of the returned range as a string into an array and loop through each delimited string, but I was wondering if there is a simpler method around this.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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