http://www.thespreadsheetg
then you find and remove the value you don't need
then you apply this array to your filter.
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
Title | # Comments | Views | Activity |
---|---|---|---|
any soltion create a connection to import XML file from website | 4 | 20 | |
File size limit in SharePoint 2010 | 3 | 14 | |
Redacting a row in Excel based on a term. | 17 | 29 | |
ACCESS 2010 Can't Open accdb - Goes To Available Templates Pane | 17 | 12 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
10 Experts available now in Live!