Adriana de Gregorio
asked on
How to speed up filters in Excel 2010?
Hello - kindly refer to the attached doc. Is it possible to improve speed of Macro/filter?
1. Data Tab - when click "all" button, takes too long.
2. When move to CPS Tab - looks like a macro is running taking too long
3. CPS Tab - when click on any of the macros along the top - they all take too long (especially "all" and "GBP"
4. Is it possible to have "GBP" macro work properly without having to click "all" first.
Let me know if you are able to help,
Regards,
AD
1. Data Tab - when click "all" button, takes too long.
2. When move to CPS Tab - looks like a macro is running taking too long
3. CPS Tab - when click on any of the macros along the top - they all take too long (especially "all" and "GBP"
4. Is it possible to have "GBP" macro work properly without having to click "all" first.
Let me know if you are able to help,
Regards,
AD
ASKER
ASKER
just attached doc
not a solutions as such but i put some timer output into the ALL code and it reported 1.9 seconds for the test data.
of this, 0.2 sec (ie 10%) was used to clear the filter on the second line.
and 1.3 sec ( 68%) was taken to copy all the formula to a "safe" area.
so it is all the sparse range copy that is taking the time....
of this, 0.2 sec (ie 10%) was used to clear the filter on the second line.
and 1.3 sec ( 68%) was taken to copy all the formula to a "safe" area.
so it is all the sparse range copy that is taking the time....
Debug.Print 8, Timer
Range("I1,K1,M1,O1,Q1,S1,U1,W1,Y1,AA1,AC1,AE1,AG1,AI1,AK1,AM1,AO1,AQ1,AS1,AU1,AW1,AY1,BA1,BC1,BE1,BG1,BI1,BK1,BM1,BO1,BQ1,BS1,BU1,BW1,BY1").Select
Range("BY1").Activate
ActiveSheet.Paste
Debug.Print 9, Timer
and i dont understand why you copy to those particular output ranges.
Part of the problem might be that your copy range overlaps with your target range (BY1 in both ranges)
Removing the BY1 from the source range, I change it from a copy/paste operation to an immediate transfer. It happens pretty quickly (0.001953125 seconds on my laptop).
Removing the BY1 from the source range, I change it from a copy/paste operation to an immediate transfer. It happens pretty quickly (0.001953125 seconds on my laptop).
Sub timeit()
Dim sngStart As Single
sngStart = Timer
Range("I1,K1,M1,O1,Q1,S1,U1,W1,Y1,AA1,AC1,AE1,AG1,AI1,AK1,AM1,AO1,AQ1,AS1,AU1,AW1,AY1,BA1,BC1,BE1,BG1,BI1,BK1,BM1,BO1,BQ1,BS1,BU1,BW1").Copy Range("BY1")
Debug.Print "Elapsed time: " & Timer - sngStart
End Sub
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
don't use the clipboard.
avoid unnecessary select and activate
avoid unnecessary select and activate
ASKER
Thank you for your response. Is it possible to update the macros to fix speed issue directly in the file so I can test.
sure, you can. Press Alt+F11 to get into the VB editor.
'<<<< Akimark code corrections to remove overlap in copy & destination
Range("I1,K1,M1,O1,Q1,S1,U1,W1,Y1,AA1,AC1,AE1,AG1,AI1,AK1,AM1,AO1,AQ1,AS1,AU1,AW1,AY1,BA1,BC1,BE1,BG1,BI1,BK1,BM1,BO1,BQ1,BS1,BU1,BW1").Copy Range("BY1")
Range("CA1,CC1,CE1,CG1,CI1,CK1,CM1,CO1,CQ1,CS1,CU1,CW1,CY1,DA1,DC1,DE1,DG1,DI1,DK1,DM1,DO1,DQ1,DS1,DU1,DW1,DY1,EA1,EC1,EE1,EG1,EI1,EK1,EM1,EO1,EQ1,ES1,EU1,EW1,EY1,FA1,FC1,FE1,FG1,FI1,FK1,FM1,FO1,FQ1,FS1,FU1,FW1").Copy Range("FY1")
Range("GA1,GC1,GE1,GG1,GI1,GK1,GM1,GO1,GQ1,GS1,GU1,GW1,GY1,HA1,HC1,HE1,HG1,HI1,HK1,HM1,HO1,HQ1,HS1,HU1,HW1,HY1,IA1,IC1,IE1,IG1,II1,IK1,IM1,IO1,IQ1,IS1,IU1,IW1,IY1,JA1,JC1,JE1,JG1,JI1,JK1,JM1,JO1,JQ1,JS1,JU1,JW1").Copy Range("JY1")
Range("KA1,KC1,KE1,KG1,KI1,KK1,KM1,KO1,KQ1,KS1,KU1,KW1,KY1,LA1,LC1,LE1,LG1,LI1,LK1,LM1,LO1,LQ1,LS1,LU1,LW1,LY1,MA1,MC1,ME1,MG1,MI1,MK1,MM1,MO1,MQ1,MS1,MU1,MW1,MY1,NA1,NC1,NE1,NG1,NI1,NK1,NM1,NO1,NQ1,NS1,NU1,NW1,NY1,OA1,OC1,OE1,OG1,OI1,OK1,OM1").Copy Range("OO1")
'<<<<<<<<<<<
IMT-NG---Foot-Care3.xlsm
@robr
What performance improvements are you seeing now?
What performance improvements are you seeing now?
in the version i uploaded, very little !!
but when i started again with the original file with just your modification to not overlap the copy/paste zones..
so the whole process takes 0.45sec, with the copy paste (8-12) taking 0.05sec
IMT-NG---Foot-Care2-trial2.xlsm
but when i started again with the original file with just your modification to not overlap the copy/paste zones..
V2-trial2
1 42417.65
3 42417.84
4 42417.84
5 42417.93
7 42417.94
8 42417.94
9 42417.95
10 42417.97
11 42417.98
12 42418
13 42418
14 42418.1
so the whole process takes 0.45sec, with the copy paste (8-12) taking 0.05sec
IMT-NG---Foot-Care2-trial2.xlsm
ASKER
Thank you for everyone's efforts but looks like still very slow when click on CPS tab. Also when click on macros in CPS also takes too long. Anyway we can modify VBA to help speed up?
find out where it is slow. the changes Akimark proposed makes a significant difference.
but how 'slow' does it matter really ?
but how 'slow' does it matter really ?
it is a BIG workbook, being used much like a database.
I suspect there are some calculations that are at the heart of your performance problem. You might want to disable automatic calculations and see what effect that has on performance.
I suspect there are some calculations that are at the heart of your performance problem. You might want to disable automatic calculations and see what effect that has on performance.
ASKER
the data tab looks good. It is the CPS Tab: each time I move from data tab to CPS tab it takes 45-60sec waiting to refresh. And the "all" Macro in the CPS tab also takes quite a bit of time.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Another issue is that this workbook has a very large number of unused styles (15,509 by my count in the XML data). Removing them shrinks the workbook down from 2,645KB to 1,887KB and speeds up processing noticeably.
I've updated the sample workbook with no other changes just so you can test performance. There's still some lag that I would not expect from a workbook this size.
-Glenn
EE-IMT-NG---Foot-Care2.xlsm
I've updated the sample workbook with no other changes just so you can test performance. There's still some lag that I would not expect from a workbook this size.
-Glenn
EE-IMT-NG---Foot-Care2.xlsm
@Glenn
How tight are the usedranges for each worksheet?
How tight are the usedranges for each worksheet?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
can you say "Access"?
how about "Crystal Reports"?
how about "Crystal Reports"?