0

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

Igor
  • 7
  • 3

1 Answers1

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.

Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69
  • '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? – Igor Jul 20 '17 at 11:34
  • Try corrected code. You are right about copying. Try extending this behaviour, so it meets your requirements. – Michał Turczyn Jul 20 '17 at 11:39
  • Set rngToFilter = Sheets("Dane magazynu").Range("J1:J100000") <--- error: Subscript out of range – Igor Jul 20 '17 at 11:50
  • You don't have that many rows as you specified... I think `J100` will be enough. – Michał Turczyn Jul 20 '17 at 12:15
  • Well in the future it may reach 20k rows, but error occurrs regardless of number of rows (100 or 100000) – Igor Jul 20 '17 at 12:20
  • I corrected the name of a sheet. It should work now. With 20k rows it will work, but not with 100 000, as you tried... But if you want to work with big data, I would change approach - the current way is inefficient. – Michał Turczyn Jul 20 '17 at 12:22
  • If Not rng.Find(cell.Value) Is Nothing Then <---- error: "Type mismatch" ;/ – Igor Jul 20 '17 at 12:47
  • Weird, I don't get that error. Works perfectly for me. – Michał Turczyn Jul 20 '17 at 12:56
  • Ok i think i know what the issue is. in col E (Dostawcy sheet) i have names of countries (USA, Germany etc.), but in "Dane magazyn" sheet sometimes I have "#N/A" instead of country name (which is fine). What your code did is, it copied 8 first entries (all USA) and crashed at 9th entry - first appearance of "#N/A". I hope it will help – Igor Jul 20 '17 at 13:04
  • The easiest way will be to add error handler. Hope it will work this time. – Michał Turczyn Jul 20 '17 at 14:20