Results 1 to 8 of 8

Thread: Filtering based on arrays

  1. #1
    New Member
    Join Date
    Sep 2014
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Filtering based on arrays

    I have a sheet that can contain upwards of 1500-2000 rows. I am trying to get VBA to filter based on certain if criteria matches items pull into an array. Part of my code work perfectly since the column (Column U) only have minimal criteia to filer by to begin with.

    Column D on the other hand has upwards of 2000 items to filter by based on the month of the report and it too large so i am pulling the data into an array. What i want to do is have column D match only 7 items from the array it created from that column and filter out only those 7 items leaving the remainder, is that possible? any help would be appreciated.

    Code:
    Sub Array_Filter()
    
    Dim i As Long
    Dim myArr As Variant
        
        'Pulls Array from column D
        myArr = Range("$D$2:$D$2000")
        
        'Filters Column U and works correctly since it has minimal createria to be able to filter by
        ActiveSheet.Range("$U$2:$U$2000").AutoFilter Field:=21, Criteria1:=Array( _
            "ColumnU item 1", "ColumnU item 2", "ColumnU item 3"), Operator:=xlFilterValues
            
        'Filter Column D where 7 criteria items match array created from Column D in myArr
    Sheet3.Range("D2").AutoFilter _
        Field:=4, _
        Criteria1:=myArr("ColumnD item 1", "ColumnD item 2", "ColumnD item 3", "ColumnD item 4", "ColumnD item 5", "ColumnD item 6", "ColumnD item 7"), _
        Operator:=xlFilterValues
        
    End Sub

  2. #2
    Board Regular
    Join Date
    May 2017
    Posts
    469
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filtering based on arrays

    I may be misunderstanding what you are trying to do, but are you sure that the Col D filter is not working because the range you gave it to filter is only one cell in size (Sheet3.Range("D2").AutoFilter)? Wouldn't something like this be better?

    Code:
        'Filter Column D where 7 criteria items match array created from Column D in myArr
        myArr.AutoFilter Field:=4, Criteria1:=Array("ColumnD item 1", "ColumnD item 2", "ColumnD item 3", "ColumnD item 4", "ColumnD item 5", "ColumnD item 6", "ColumnD item 7"), Operator:=xlFilterValues
    Last edited by rlv01; Jul 27th, 2019 at 02:33 PM.

  3. #3
    New Member
    Join Date
    Sep 2014
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filtering based on arrays

    Quote Originally Posted by rlv01 View Post
    I may be misunderstanding what you are trying to do, but are you sure that the Col D filter is not working because the range you gave it to filter is only one cell in size (Sheet3.Range("D2").AutoFilter)? Wouldn't something like this be better?

    Code:
        'Filter Column D where 7 criteria items match array created from Column D in myArr
        myArr.AutoFilter Field:=4, Criteria1:=Array("ColumnD item 1", "ColumnD item 2", "ColumnD item 3", "ColumnD item 4", "ColumnD item 5", "ColumnD item 6", "ColumnD item 7"), Operator:=xlFilterValues
    I might not have been super clear, my bad. but column D can contain anywhere from a few 100 rows to 2000 rows, and possibly more depending on the month. What I need to do is filter out only 7 items (possibly more) from this and leave the remainder. It is possible from month to month that the 7 or more items that need to be filtered out could change from month to month and I would build a prompt to ask for what items would need to be filtered out via a message box to a formula to seek out the new list to filter out those items.

    The final expectation is that there could possibly be more than 50k rows and 100's of items that would need to be filtered out from the report but hat has not been finalized if we cant get VBA to work correctly on the limited data.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,177
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Filtering based on arrays

    If you put the list of items to exclude in a list somewhere (I've used col X), you could use
    Code:
    Sub Mike76053()
    
       Dim i As Long
       Dim myArr As Variant, Exclude As Variant
        
       'Pulls Array from column D
       myArr = Range("D2", Range("D" & Rows.Count).End(xlUp)).Value2
       Exclude = Range("X2", Range("X" & Rows.Count).End(xlUp)).Value2
       With CreateObject("scripting.dictionary")
          For i = 1 To UBound(ary)
             .Item(ary(i, 1)) = Empty
          Next i
          For i = 1 To UBound(Exclude)
             If .Exists(Exclude(i, 1)) Then .Remove Exclude(i, 1)
          Next i
          
          Sheet3.Range("A2:D2").AutoFilter 4, .Keys, xlFilterValues
        End With
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    New Member
    Join Date
    Sep 2014
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filtering based on arrays

    Originally Posted by Fluff If you put the list of items to exclude in a list somewhere (I've used col X), you could use
    Code:
    Sub Mike76053() 
    
       Dim i As Long
       Dim myArr As Variant, Exclude As Variant
        
       'Pulls Array from column D
       myArr = Range("D2", Range("D" & Rows.Count).End(xlUp)).Value2
       Exclude = Range("X2", Range("X" & Rows.Count).End(xlUp)).Value2
       With CreateObject("scripting.dictionary")
          For i = 1 To UBound(ary)
             .Item(ary(i, 1)) = Empty
          Next i
          For i = 1 To UBound(Exclude)
             If .Exists(Exclude(i, 1)) Then .Remove Exclude(i, 1)
          Next i
          
          Sheet3.Range("A2:D2").AutoFilter 4, .Keys, xlFilterValues
        End With
    End Sub

    in the below line i put the list of data into column BX

    Code:
       Exclude = Range("X2", Range("X" & Rows.Count).End(xlUp)).Value2
    so i changed the code to if that is correct:
    Code:
       Exclude = Range("BX2", Range("BX" & Rows.Count).End(xlUp)).Value2
    when i run the script i get error Object Required and when i debug i highlights the below code line.

    Code:
          Sheet3.Range("A2:D2").AutoFilter 4, .Keys, xlFilterValues

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,177
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Filtering based on arrays

    Couple of typos in there, try
    Code:
    Sub Mike76053()
    
       Dim i As Long
       Dim myArr As Variant, Exclude As Variant
        
       'Pulls Array from column D
       myArr = Range("D2", Range("D" & Rows.Count).End(xlUp)).Value2
       Exclude = Range("BX2", Range("BX" & Rows.Count).End(xlUp)).Value2
       With CreateObject("scripting.dictionary")
          .comparemode = 1
          For i = 1 To UBound(myArr)
             .Item(myArr(i, 1)) = Empty
          Next i
          For i = 1 To UBound(Exclude)
             If .Exists(Exclude(i, 1)) Then .Remove Exclude(i, 1)
          Next i
          
         Range("A2:D2").AutoFilter 4, .Keys, xlFilterValues
        End With
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    New Member
    Join Date
    Sep 2014
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filtering based on arrays

    Quote Originally Posted by Fluff View Post
    Couple of typos in there, try
    Code:
    Sub Mike76053()
    
       Dim i As Long
       Dim myArr As Variant, Exclude As Variant
        
       'Pulls Array from column D
       myArr = Range("D2", Range("D" & Rows.Count).End(xlUp)).Value2
       Exclude = Range("BX2", Range("BX" & Rows.Count).End(xlUp)).Value2
       With CreateObject("scripting.dictionary")
          .comparemode = 1
          For i = 1 To UBound(myArr)
             .Item(myArr(i, 1)) = Empty
          Next i
          For i = 1 To UBound(Exclude)
             If .Exists(Exclude(i, 1)) Then .Remove Exclude(i, 1)
          Next i
          
         Range("A2:D2").AutoFilter 4, .Keys, xlFilterValues
        End With
    End Sub
    works great. thank you

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,177
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Filtering based on arrays

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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
  •