Solved

How to speed up filters in Excel 2010?

Posted on 2014-09-25
23
240 Views
Last Modified: 2014-10-02
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
0
Comment
Question by:Adriana de Gregorio
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
  • 5
  • +1
23 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 40345808
kindly refer to the attached doc
Nothing is attached.
0
 

Author Comment

by:Adriana de Gregorio
ID: 40345812
0
 

Author Comment

by:Adriana de Gregorio
ID: 40345814
just attached doc
0
Save the day with this special offer from ATEN!

Save 30% on the CV211 using promo code EXPERTS30 now through April 30th. The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 40347086
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....
 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

Open in new window

and i dont understand why you copy to those particular output ranges.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40347505
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).
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

Open in new window

0
 
LVL 32

Assisted Solution

by:Robberbaron (robr)
Robberbaron (robr) earned 125 total points
ID: 40347564
great pickup Akimark!

needs to be done on all the copies...

immediate reduction from 1.3sec to 0.08 sec.

 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").Select
 Range("BY1").Activate
 ActiveSheet.Paste
  Debug.Print 9, Timer
 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").Select
 Range("FY1").Activate
 ActiveSheet.Paste
  Debug.Print 10, Timer
 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").Select
 Range("JY1").Activate
 ActiveSheet.Paste
  Debug.Print 11, Timer
 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").Select
 Range("OO1").Activate
 ActiveSheet.Paste

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
ID: 40347590
don't use the clipboard.
avoid unnecessary select and activate
0
 

Author Comment

by:Adriana de Gregorio
ID: 40349896
Thank you for your response.  Is it possible to update the macros to fix speed issue directly in the file so I can test.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40350008
sure, you can.  Press Alt+F11 to get into the VB editor.
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 40350016
    
    '<<<< 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")
     '<<<<<<<<<<<
     

Open in new window

IMT-NG---Foot-Care3.xlsm
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40350306
@robr

What performance improvements are you seeing now?
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 40351372
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..
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 

Open in new window


so the whole process takes 0.45sec, with the copy paste (8-12) taking 0.05sec
IMT-NG---Foot-Care2-trial2.xlsm
0
 

Author Comment

by:Adriana de Gregorio
ID: 40351955
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?
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 40352004
find out where it is slow. the changes Akimark proposed makes a significant difference.

but how 'slow' does it matter really ?
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40352112
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.
0
 

Author Comment

by:Adriana de Gregorio
ID: 40352178
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.
0
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 125 total points
ID: 40352300
If you change the calculation setting from automatic to manual, does the performance still suck?
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40353659
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
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40353674
@Glenn

How tight are the usedranges for each worksheet?
0
 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 250 total points
ID: 40353683
@aikimark, I'm glad you mentioned that!  I also had gone through and removed extraneous rows and columns from all sheets.  That also had an effect on shrinking the file size, but I didn't measure it specifically.

There's a hidden row on the CPS sheet (205), but for what purpose, I haven't determined.  (Edit: they appear to just be concatenated values in rows 3&4 used for Vlookup...could easily be replaced)

Also, I note that there are a huge number of formulas on the hidden "Vlookup Data" sheet that return blanks.  These formulas appear to be "leftovers" and in fact, their alignment has been corrupted.  columns X:CI are shifted down by three rows.  In any case, there are 9,216 formulas like this:
=IFERROR(INDEX(Project_Name, SMALL(IF(('Vlookup Data'!P$2=QTRS)*(class='Vlookup Data'!P$3), ROW(Name)-MIN(ROW(Name))+1), ROW(114:114))),"")
that serve no purpose, IMO.
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 250 total points
ID: 40353755
Also, I just discovered there are 72 conditional formatting rules on the CPS sheet.  There are almost 9,700 formulas - most of which are used to generate value for the conditional formatting.

There has to be a way to bring this total down.  I can't see all these columns being necessary all the time.  Perhaps this sheet could be converted to a true reporting sheet with the data stored elsewhere.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40353759
can you say "Access"?
how about "Crystal Reports"?
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question