Hello all,
Could you please help me optimizing VBA?
I got series of VBA scripts running every hour and I think it can be optimized as I have seen excel being not responded for over 12 minutes so I believe it just needs optimization. Please see code below:
Please Login or Register to view this content.
Hi !
This bad code (full of Select, Selection, awfull !) ends in tenth of a second !
So if this code runs over 12 minutes, the truth is elsewhere …
No, this is only the part of a code that I'm asking you guys to optimize.Originally Posted by Marc L
Hi !
This bad code (full of Select, Selection, awfull !
So if this code runs over 12 minutes, the truth is elsewhere …
Please Login or Register to view this content.
I'd do something like below.
OptimizeVBA is trick I learned at http://analystcave.com/Please Login or Register to view this content.
Try worksheets ("S11").rows("1:" & rows.count).clearcontents for each sheet.
The previous reply indicates, the code you provided would in no way run for 12 minutes, there must be some other code that is kicking in that is causing your code to run for a long time, possibly a wack of formulas could be causing the issue. Then set your calculate to manual.
Anyway, you can get rid of your selects,
Can be modified to possiblyPlease Login or Register to view this content.
Please Login or Register to view this content.
Last edited by davesexcel; 12-13-2016 at 02:12 PM.
Please Login or Register to view this content.
My General Rules if you want my help. Not aimed at any person in particular:
1. Please Make Requests not demands, none of us get paid here.
2. Check back on your post regularly. I will not return to a post after 4 days.
If it is not important to you then it definitely is not important to me.
Please Login or Register to view this content.
What if I would like to perform the same task but across sheets that are named differently. For example "AMPL" YEHK" "UIOS". Would it be possible to perform the task as efficiently as if a ran on sheets named S0*
Yes for example if their indexes are closed like worksheet #4 to #6 …
Or like post #7 …
Which option is quicker to run? My project is still in early stages so i'm able to change the structure of it.
Please Login or Register to view this content.
Im trying to run this code below, but it doesnt seem to work. Compiler highlighting line 5.Please Login or Register to view this content.
Please Login or Register to view this content.
Last edited by ChipsSlave; 12-13-2016 at 04:31 PM.
Your range has to be qualified as well
Look at my original code, that is why I used with and end withPlease Login or Register to view this content.
I'm sorry but I don't follow. Could you please explain me this a bit more.Your range has to be qualified as well
Look at my original code, that is why I used with and end withPlease Login or Register to view this content.
Sorry, this version works.
Please Login or Register to view this content.
I can confirm it works. Thank you for your support.
@ChipsSlave
Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding
For normal conversational replies, try using the QUICK REPLY box below.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
I'm sorry for bugging you, but could you show me how do I optimize this code?
It should filter table COMP_summ_9 by DTTD, FDTL,... and then select A2:C2 ctrl+shift down and copy selection to the DTTD,FDTL,... sheets and paste it into A3 cell. I was able to optimize other bits of the code just this one seems a little bit to difficult to me as I am not an expert of VBA. Thank you.
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
,
Bookmarks