The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!
Sub Test()
Dim N As Long, r As Range
With Sheets("CriteriaSheet")
N = .Cells(Rows.Count, "A").End(xlUp).Row
ReDim arry(1 To N)
For i = 1 To N
arry(i) = .Cells(i, 1)
Next i
End With
On Error Resume Next
Range("$A$1:$N$72866").AutoFilter
ActiveSheet.Range("$A$1:$N$72866").AutoFilter Field:=2, Criteria1:=arry, Operator:=xlFilterValues
On Error GoTo 0
End Sub
EE.xlsm
Sub Test()
Dim Arry
With Sheets("CriteriaSheet")
Set Rng = Range(.Range("A1"), .Cells(Rows.Count, "A").End(xlUp))
Arry = WorksheetFunction.Index(WorksheetFunction.Transpose(Rng), 1, 0)
End With
On Error Resume Next
Set rngFilter = ActiveSheet.UsedRange
rngFilter.AutoFilter
rngFilter.AutoFilter Field:=13, Criteria1:=Arry, Operator:=xlFilterValues
On Error GoTo 0
End Sub
Regards
Sub Test()
Dim N As Long, r As Range
With Sheets("CriteriaSheet")
N = .Cells(Rows.Count, "A").End(xlUp).Row
ReDim arry(1 To N)
For i = 1 To N
arry(i) = .Cells(i, 1)
Next i
End With
On Error Resume Next
Range("$A$1:$N$72866").AutoFilter
ActiveSheet.Range("$A$1:$N$72866").AutoFilter Field:=13, Criteria1:=arry, Operator:=xlFilterValues
On Error GoTo 0
End Sub
Sub Test()
Dim Arry
With Sheets("CriteriaSheet")
Set Rng = Range(.Range("A1"), .Cells(Rows.Count, "A").End(xlUp))
If Rng.Cells.Count > 1 Then
Arry = WorksheetFunction.Index(WorksheetFunction.Transpose(Rng), 1, 0)
Else
Arry = Array(Rng.Value)
End If
End With
Arry = Filter(Arry, "System Entries", False)
On Error Resume Next
Set rngFilter = ActiveSheet.UsedRange
rngFilter.AutoFilter
rngFilter.AutoFilter Field:=1, Criteria1:=Arry, Operator:=xlFilterValues
On Error GoTo 0
End Sub
Sub Test()
Dim Arry
With Sheets("Sheet1")
.Activate
Set Rng = Range(.Range("M1"), .Cells(Rows.Count, "M").End(xlUp))
If Rng.Cells.Count > 1 Then
Arry = WorksheetFunction.Index(WorksheetFunction.Transpose(Rng), 1, 0)
Else
Arry = Array(Rng.Value)
End If
End With
Arry = Filter(Arry, "System Entries", False)
On Error Resume Next
Set rngFilter = ActiveSheet.UsedRange
rngFilter.AutoFilter
rngFilter.AutoFilter Field:=13, Criteria1:=Arry, Operator:=xlFilterValues
On Error GoTo 0
End Sub
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
http://www.thespreadsheetg
then you find and remove the value you don't need
then you apply this array to your filter.