Filtering based on arrays

mike760534211

New Member
Joined
Sep 25, 2014
Messages
13
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top