Adding values as array's in AutoFilter

[maxblack]

Hello Guys,

I need your help with following problem. I build a macro, which will be importing two xls, one with data from the report, which I generate everyday (sheets REPORT) and second xls which contain only account numbers written one under the other in separe rows (sheet ACCOUNTS).
Then when I will start macro it should delete from the report (sheets REPORT) all account numbers, which are different then these, which I imported.

I was thinking to using AutoFilter but the problem is how to add all of these accounts as Array's to the Autofilter.

Normaly I use to add things, which I would like to delete or do something inside the code, but I would like to make a step forward and make it more practicial, thats why I need you help Excel Gods

For now I have only AutoFilter code - like below:

Dim wb As Workbook
Set wb = ThisWorkbook

With wb.Sheets("REPORT").Range("A1").CurrentRegion
        .AutoFilter field:=1, Criteria1:=?????accounts <> then imported?????
        .Offset(1, 0).Resize(wb.Sheets(1).Range("A1").CurrentRegion.rows.Count - 1).rows.Delete
End With

Thanks a lot!

 

[James006]

Hi,

You could adapt the following macro to your specific situation

[COLOR=#000000][FONT=Consolas][FONT=inherit]Option[/FONT][FONT=inherit]Explicit
[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas][FONT=inherit]Sub[/FONT][FONT=inherit]Excel_VBA_AutoFilter_Using_Multiple_Criteria_List[/FONT][FONT=inherit]([/FONT][FONT=inherit])[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas][FONT=inherit]
'Variables Declaration[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas][FONT=inherit]Dim [/FONT][FONT=inherit]Criteria_Val[/FONT][FONT=inherit]([/FONT][FONT=inherit]100[/FONT][FONT=inherit])[/FONT][FONT=inherit]As [/FONT][FONT=inherit]String[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas][FONT=inherit]Dim [/FONT][FONT=inherit]iRow [/FONT][FONT=inherit]As [/FONT][FONT=inherit]Integer[/FONT][/FONT][/COLOR]

[COLOR=#000000][FONT=Consolas][FONT=inherit]'Loop Thru the List and Create Filter Criteria Array[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas][FONT=inherit]iRow[/FONT][FONT=inherit]=[/FONT][FONT=inherit]0[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas][FONT=inherit]While [/FONT][FONT=inherit]Sheets[/FONT][FONT=inherit]([/FONT][FONT=inherit]2[/FONT][FONT=inherit])[/FONT][FONT=inherit].[/FONT][FONT=inherit]Cells[/FONT][FONT=inherit]([/FONT][FONT=inherit]iRow[/FONT][FONT=inherit]+[/FONT][FONT=inherit]1[/FONT][FONT=inherit],[/FONT][FONT=inherit]1[/FONT][FONT=inherit])[/FONT][FONT=inherit]<>[/FONT][FONT=inherit]""[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas][FONT=inherit]   Criteria_Val[/FONT][FONT=inherit]([/FONT][FONT=inherit]iRow[/FONT][FONT=inherit])[/FONT][FONT=inherit]=[/FONT][FONT=inherit]Sheets[/FONT][FONT=inherit]([/FONT][FONT=inherit]2[/FONT][FONT=inherit])[/FONT][FONT=inherit].[/FONT][FONT=inherit]Cells[/FONT][FONT=inherit]([/FONT][FONT=inherit]iRow[/FONT][FONT=inherit]+[/FONT][FONT=inherit]1[/FONT][FONT=inherit],[/FONT][FONT=inherit]1[/FONT][FONT=inherit])[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas][FONT=inherit]   iRow[/FONT][FONT=inherit]=[/FONT][FONT=inherit]iRow[/FONT][FONT=inherit]+[/FONT][FONT=inherit]1[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas][FONT=inherit]Wend[/FONT][/FONT][/COLOR]

[COLOR=#000000][FONT=Consolas][FONT=inherit]'Multiple Criteria List is Created. Pass it as parameter to Filter[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas][FONT=inherit] Sheets[/FONT][FONT=inherit]([/FONT][FONT=inherit]1[/FONT][FONT=inherit])[/FONT][FONT=inherit].[/FONT][FONT=inherit]Range[/FONT][FONT=inherit]([/FONT][FONT=inherit]"A1:A10"[/FONT][FONT=inherit])[/FONT][FONT=inherit].[/FONT][FONT=inherit]AutoFilter [/FONT][FONT=inherit]Field[/FONT][FONT=inherit]:[/FONT][FONT=inherit]=[/FONT][FONT=inherit]1[/FONT][FONT=inherit],[/FONT][FONT=inherit]Criteria1[/FONT][FONT=inherit]:[/FONT][FONT=inherit]=[/FONT][FONT=inherit]Criteria_Val[/FONT][FONT=inherit],[/FONT][FONT=inherit]Operator[/FONT][FONT=inherit]:[/FONT][FONT=inherit]=[/FONT][FONT=inherit]xlFilterValues[/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas][FONT=inherit]End [/FONT][FONT=inherit]Sub[/FONT][/FONT][/COLOR]

HTH

 

[maxblack]

Thanks James006, your code is working, but finally I have used following:

<code class="vbscript hljs">Dim wb As Workbook
Set wb = ThisWorkbook
Dim tbl()
Dim lLstRw&

With Sheets("Accounts")
    lLstRw = .Cells(Rows.Count, 1).End(xlUp).Row
    tbl() = Application.Transpose(Range(.Cells(2, 1), .Cells(lLstRw, 1)))
End With
    
With wb.Sheets("REPORT").Range("A1").CurrentRegion
        .AutoFilter field:=1, Criteria1:=tbl(), Operator:=xlFilterValues
        .Offset(1, 0).Resize(wb.Sheets(1).Range("A1").CurrentRegion.Rows.Count - 1).Rows.Delete
End With</code>

 

[James006]

Hi,

Thanks ... for your thanks ... :wink:

Glad you could solve your problem ...