VBA – Array Filter

[teeitup16]

I’ve created a loop macro which results in the attached summary sheet for each value tested. Prior to the next loop the code currently uses a Data Filter within excel to select only the required lines. The filtered lines are copied and moved to a data summary tab and the Data Filter is released for the next value tested within the loop. The code works but the time associated with the each filter has cause the macro to take 10 or 15 minutes to complete.

I believe I should be using a filter array to select and copy the data instead of the Data Filter. I’ve researched Array filters but don’t seem to fully fit what I’m trying to solve.

Thank you so much for your any assistance you can provide.

Doug

[VBA]
Sheets("Trade Limiter").Select
Columns("A:Q").Select
Selection.AutoFilter
ActiveSheet.Range("A:Q").AutoFilter Field:=12, Criteria1:="<>"
Range("Q1").Select
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
` Sheets("Trade Data").Select
ActiveSheet.Paste
[/VBA]

[Kenneth Hobs]

Try my routines in: http://vbaexpress.com/kb/getarticle.php?kb_id=1035

[teeitup16]

Ken,

Thanks for the quick reply - switching over to manual calculation improved the speed to only 3-4 minutes. I'd still love any suggestion as to how to place the data into an array.

Thanks Again!!

[mohanvijay]

can you please post the workbook with your code and data?

[teeitup16]

can you please post the workbook with your code and data?

File and code enclosed. Thanks for your help.