I need your expertise. I use below code to Auto Filter from another sheet named "FilterList" where I have to filter 15 k rows or more every month. I have 2 VBA scripts which I use for Autofilter . But now I have a requirement to Filter all items "NOT" in the list. You can use VBA Script 1 Or VBA Script2 . But I prefer 1 (if possible) because it runs faster. but not an issue with the script 2 if not possible with 1
In the script 2 i have created a line rng.AutoFilter Field:=e, Criteria1:="<>" & Criteria, Operator:=xlFilterValues to Filter items not in the list but its does not show any result.
I use ribbon button command from personal.xlsb I will use this as a separate button. Thanks as always.
VBA Script 1 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Code: Select all
Sub InverseFilter1()
Application.ScreenUpdating = False
Const m = "MESSAGE", S = "FilterList"
With Sheets
If .Count > 1 Then
'.Item(1).Activate
If Selection.Column > ActiveSheet.UsedRange.Columns.Count Then Msgbox "Select a Column within the range { " & ActiveSheet.UsedRange.Cells.Address & " } to Filter !", 64, m _
Else ActiveSheet.UsedRange.AutoFilter Selection.Column, Application.Transpose(Sheets("FilterList").UsedRange), 7
Else
'.add(, .Item(1)).Name = "FilterList"
Msgbox "Add your search list in column A and proceed!!", 64, m
End If
End With
Application.ScreenUpdating = True
End Sub]
VBA Script 2 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
I tried to add this line " 'rng.AutoFilter Field:=e, Criteria1:="<>" & Criteria, Operator:=xlFilterValues " at the bottom of the script where I commented out.
Code: Select all
Sub InverseFilter2 ()
Application.ScreenUpdating = False
Dim tempCriteria As Variant
Dim i As Long
Dim Criteria() As String
Dim rng As Range
Dim e As Integer
Dim FilterList As Worksheet
Dim ShtName As String
ShtName = "Filterlist"
Set rng = ActiveSheet.UsedRange
LastRow = Sheets("Filterlist").Cells(Rows.Count, 1).End(xlUp).Row
e = rng.Application.WorksheetFunction.Match(Selection, Range("1:1"), 0)
tempCriteria = Sheets("FilterList").Range("A1:A" & LastRow)
'tempCriteria = Worksheets("FilterList").Range("A1:A" & lastRow)
ReDim Criteria(1 To UBound(tempCriteria))
For i = 1 To UBound(tempCriteria)
Criteria(i) = CStr(tempCriteria(i, 1))
Next
rng.AutoFilter field:=e, Criteria1:=Criteria, Operator:=xlFilterValues
'rng.AutoFilter Field:=e, Criteria1:="<>" & Criteria, Operator:=xlFilterValues <<<< Need help here
Application.ScreenUpdating = True
End Sub]