Filtering based on arrays

[mike760534211]

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.

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

 

[rlv01]

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?

    '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

 

[mike760534211]

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?

    '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.

 

[Fluff]

If you put the list of items to exclude in a list somewhere (I've used col X), you could use

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

 

[mike760534211]

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

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

   Exclude = Range("X2", Range("X" & Rows.Count).End(xlUp)).Value2

so i changed the code to if that is correct:

   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.

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

 

[Fluff]

Couple of typos in there, try

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

 

[mike760534211]

Couple of typos in there, try

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

 

[Fluff]

You're welcome & thanks for the feedback