Filter column based on array from another sheet

Question

I have three sheets called: "Dane magazyn", "Sheet3" and "Dostawcy". What I want my Excel to do is:

1) filter out #N/A values in col. J on sheet "Dane magazyn". All values that should stay after filtering are stored in Col. E on sheet "Dostawcy" - 21 entries, but it will be more in the future.

2) select data that remained after filtering and copy to "Sheet3"

Here's my code so far:

Sub filtruj()
Dim i As Long, arr As Variant, LastRow As Long
Sheets("Dostawcy").Select
With ActiveSheet
    LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row
End With
arr = Sheets("Dostawcy").Range("E2:E" & LastRow).Value
Sheets("Dane magazyn").Select
With ActiveSheet
**.Columns("J").AutoFilter Field:=1, Criteria1:=arr, Operator:=xlFilterValues** <---- here I get error
End With

Rest of code...

Error message I get is: "Run-time error '1004':

AutoFilter method of Range class failed"

websites I've checked (not all listed)

Using string array as criteria in VBA autofilter

VBA assign a range to an Array from different sheet

Fastest way to read a column of numbers into an array

Thanks in advance

Michał Turczyn · Accepted Answer · 2017-07-20 14:19:29Z

0

Here is working code:

Dim rng, rngToFilter As Range
Dim i As Integer: i = 1
'set you range to area with values to compare against
'if you can, specify here exact range instead of whole column, it can increase efficiency
Set rng = Sheets("Dostawcy").Range("E:E")

'set range to be filtered out, don't specify here whole column,
'in following loop it can result in overflow error
Set rngToFilter = Sheets("Dane magazyn").Range("J1:J100")
'here we will iterate through all cells within the searched range,
'for every cell we will try to find it's value within the other range,
'if it succeeds, so it's not Nothing, then we copy it to Sheet3
For Each cell In rngToFilter
    'if any cell causes the error, we will skip one iteration
    On Error GoTo ErrorHandler:
    If Not rng.Find(cell.Value) Is Nothing Then
        Sheets("Sheet3").Cells(i, 1).Value = cell.Value
        i = i + 1
    End If
    ErrorHandler:
Next cell

Don't use Select unless you must, it reduces efficiency of a program.

edited Jul 20, 2017 at 14:19

answered Jul 20, 2017 at 11:27

Michał Turczyn

41.3k18 gold badges58 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Igor Over a year ago

'For Each cell In rngToFilter If Not rngFind(cell.Value) Is Nothing Then <--- i get error here' "Sub or function not defined. Plus i think it will only copy Col. J to "sheet3", won't it? I'd like it to copy all rows that remained after filtering. Is this possible?

Michał Turczyn Over a year ago

Try corrected code. You are right about copying. Try extending this behaviour, so it meets your requirements.

Igor Over a year ago

Set rngToFilter = Sheets("Dane magazynu").Range("J1:J100000") <--- error: Subscript out of range

Michał Turczyn Over a year ago

You don't have that many rows as you specified... I think J100 will be enough.

Igor Over a year ago

Well in the future it may reach 20k rows, but error occurrs regardless of number of rows (100 or 100000)

|

Collectives™ on Stack Overflow

Filter column based on array from another sheet

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related