I have the following code which runs through over 140K records and makes changes base on the code below. Currently, it is taking 20to 30 min to run this simple macro. Is there any way to speed up the code below to cut down the processing time?Thanks
Please Login or Register to view this content.
Please Login or Register to view this content.
Last edited by AB33; 12-28-2012 at 06:25 PM.
changing the
intoPlease Login or Register to view this content.
Please Login or Register to view this content.
I got compile error: Elseif without If. Thanks
Turn off automatic calculations and screen updating and bunch all in one loop...
Try the macro attached
Try this on a copy of your workbook
Please Login or Register to view this content.
The 1,2,3,4,5 and the A,b,c,d,e were samples. I actually have anumber like 401, 402,403,404,405 and then 402=Apple, 403=Orange etc. How could I alter your transform code to factor this in? Thanks
Try:
Please Login or Register to view this content.
Or you could use
Regards, TMSPlease Login or Register to view this content.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
rlsublime,
Perhaps...
Please Login or Register to view this content.
Hope that helps,
~tigeravatar
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Hi,
Assuming you have Excel 2007 or 2010, and unless you really do have over 1 million records then the simplest thing you could do would be to limit your I variable to only those column E cells that contain a record. e.g. Set I = Range("E1:E500").
However loops are inherently slow and in 99% of cases like this you should use data filtering to first filter the relevant rows and then apply your changes to the filtered cells.
In this case however then it will be even quicker to use Find & Replace. e.g
Please Login or Register to view this content.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Thank You jolivanes. That worked perfectly!
Sorry! Please see corrected code post 2
Just because I had some spare time!!!!!
On 518,400 cells on my older XP machine:
6.23 seconds
6.22 secondsPlease Login or Register to view this content.
16.39 secondsPlease Login or Register to view this content.
6.28 secondsPlease Login or Register to view this content.
Please Login or Register to view this content.
[QUOTE=jolivanes;3061685]Just because I had some spare time!!!!!
On 518,400 cells on my older XP machine:
Interesting. Perhaps not surprisingly there is a small overhead because of loops (and out of interest how many cells are in the relevant range?) and a With statement.
What's the time taken with post # 10 just as a matter of interest (obviously with all 5 elements) rather than just the two in the example?
@jolivanes, on your 6.23 solution, try moving the set rng statement to before starting the loop. I think there is no need to set the rng object for every iteration.
If you like my contribution click the star icon!
Hi Richard.
I more or less thought that your suggestion and the 2nd code would be the same. Unless the "lookat:=xlPart" makes a difference.
I did change the code to be without the xlPart but it did not make any difference on my machine.
I have cells numbered from 1 to 9 in Column E and repeat this to a total of 518,400 cells (E2:E518401)
Goedemorgen (Good Morning) Olaf
I did as you suggested but it did not take any time off the clock.
However, I think you're right that it is useless to set the rng every time.
I have attached the file so anyone can play with it on their own machine.
Please read the comments on Sheet1 in the red bordered box first. This was done to keep the file small.
Happy, Healthy and Prosperous 2013.
John
Hi Jolivanes,
Thanks for taking the trouble to put that together.
Interestingly I had similar results, albeit about two seconds slower apart from the 'Replace Some' option where it took 41.5 seconds vs your 15.69. Quite considerably longer. And you were correct in your belief that the straightforward
Range("E1:E518401").Replace What:="1", Replacement:="A" construction is more or less the same as option 2.
Hi Richard
That is a considerable difference, 15.7 vs 41.5 seconds. My computer is about 6-7 years old so definitely not the latest screaming fast machine.
Happy, Healthy and Prosperous 2013 to you and yours.
John
Yes, odd indeed. Mine is a 2008 vintage Dell Latitude, so a bit long in the tooth perhaps, but still pretty fast. I wondered if there were other apps running but I don't think so and the slow running was consistently on the macro mentioned - and I tried all of them several times.
Hi Richard.
I tried it on my 8 year old laptop (Tecra with 1GB Ram and 1.8 GHz processor) but that has Office 2003 on it so not really a valid comparison.
I saved the file in .xls format and ran the different scenarios on both computers with pretty well identical results on both.
0.83 - 0.78 - 1.97 and 0.80 seconds.
However, because of the less rows in 2003, the range to work on is now only 65,535 cells.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks