Excel doesn't allow you to use the array like you want it in the Autofilter. But is there an alternative? Yes!
Logic If I ask you choose numbers between 0 and 10 including 0 and 10 but you cannot choose 0, 5, 8 and 10. So instead of filtering the numbers out and saying I don't want 0, 5, 8 and 10, you can say I want 1,2,3,4,6,7,9.
Similarly in your case we will not filter on the values we DON'T want. We will filter on values we WANT.
So HOW do we find that list and store it in an array?
- Find the last row in the relevant column.
- Store all records from that column in a unique collection
- Check which items in that collection are not in the "exclude" list and create an array.
- Filter the range on the values(array) you want! This way we will not have to filter on the values which we don't want.
Code (Tested in Excel 2013 with 50k records)
I have commented the code but if you still have any questions then feel free to ask :)
Dim OmitArray As Variant
Const deLim As String = "|"
Sub Sample()
Dim Ws As Worksheet
Dim lRow As Long, i As Long, n As Long, lCol As Long
Dim Col As New Collection, itm
Dim includeArray As Variant
Dim rng As Range
Dim tmpString As String
'~~> This array has all the values that you want to ignore
OmitArray = Array("DRCA", "DREX", "DRFU", "DRIN", "DRIR", "DRND", _
"DRPN", "DRPR", "DRRE", "DRUN", "REXC", "EXCD", "RFUR", _
"RINV", "RIRC", "RNDR", "RPNA", "RPRO", "RRET", "RUND", _
"RUNF", "EXC", "C")
'~~> This is the column where you want to filter out
lCol = 27
'~~> Change this to the relevant worksheet
Set Ws = ThisWorkbook.Sheets("Sheet1")
With Ws
'~~> Find lastrow
lRow = .Range("A" & .Rows.Count).End(xlUp).Row
'~~> This is the range where the filter will be applied
Set rng = .Range("A1:AR" & lRow)
'~~> All all the values from col 27 to a unique collection
For i = 2 To lRow
On Error Resume Next
Col.Add .Cells(i, lCol).Value, CStr(.Cells(i, 27).Value)
On Error GoTo 0
Next i
'~~> Now loop though the collection and store the values in a string
'~~> delimited with a delimiter which arenot present in the "OmitArray"
For Each itm In Col
If Not IsInArray(itm, OmitArray) Then
If tmpString = "" Then
tmpString = itm
Else
tmpString = tmpString & deLim & itm
End If
End If
Next itm
If tmpString <> "" Then
'~~> Split the values based on the delimiter to create array
includeArray = Split(tmpString, deLim)
'~~> Remove any filters
.AutoFilterMode = False
'~~> Filter on the rest of the values
With rng
.AutoFilter Field:=lCol, Criteria1:=includeArray, Operator:=xlFilterValues
End With
End If
End With
End Sub
'~~> Function to check if there is an item in the array
Function IsInArray(stringToBeFound As Variant, arr As Variant) As Boolean
Dim bDimen As Byte, i As Long
On Error Resume Next
If IsError(UBound(arr, 2)) Then bDimen = 1 Else bDimen = 2
On Error GoTo 0
Select Case bDimen
Case 1
On Error Resume Next
IsInArray = Application.Match(stringToBeFound, arr, 0)
On Error GoTo 0
Case 2
For i = 1 To UBound(arr, 2)
On Error Resume Next
IsInArray = Application.Match(stringToBeFound, Application.Index(arr, , i), 0)
On Error GoTo 0
If IsInArray = True Then Exit For
Next
End Select
End Function
