PDA

View Full Version : VBA – Array Filter



teeitup16
10-22-2012, 04:13 PM
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


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

Kenneth Hobs
10-22-2012, 07:02 PM
Try my routines in: http://vbaexpress.com/kb/getarticle.php?kb_id=1035

teeitup16
10-22-2012, 07:51 PM
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
10-23-2012, 03:23 AM
can you please post the workbook with your code and data?

teeitup16
10-23-2012, 04:23 AM
can you please post the workbook with your code and data?

File and code enclosed. Thanks for your help.