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
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

rlv01

Well-known Member
Joined
May 16, 2017
Messages
771
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:

mike760534211

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,348
Office Version
365
Platform
Windows
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
 

mike760534211

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,348
Office Version
365
Platform
Windows
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
 

mike760534211

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,348
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,101,771
Messages
5,482,822
Members
407,365
Latest member
Leah Ashley

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top