Hi,
I have the code below to look at one sheet, and depending on the results, copy the relevant row to the relevant tab. It takes around a minute to run on my PC (checking 1000 rows), and was wondering if there was any way to optimise it? :-
I'd be glad of any ideas as I'm sure this can be done more quickly.
Please Login or Register to view this content.
Thanks in advance!
Last edited by bd528; 09-12-2010 at 08:33 AM.
You could turn off screen updating and automatic calculation, you could also stop using paste special to copy the values and instead just define a range on the Temp sheet the same size as the one on the sheet you're copying from and say.
That would save you having to activate different sheets.Please Login or Register to view this content.
Things would be lot clearer if you tidied up your case statement. A lot of things there happen whatever the case selected is, so there's no need to repeat that code and embed it inside the select statement.
Also avoid changing the active cell, you can loop through a range of cells without having to select each one in turn.
Does that get you started?
Could you sort the sheet, copy in segments and then unsort afterwards? You will need to put in a helper column with sequential numbers in order to sort back to the original order.
Martin
Hello bd528,
When coding in VBA, it is rare that you need to select or activate an object before you can perform an operation. It is clear that you learned to code from the Macro Recorder. It will take a some time to clean up and optimize the code.
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Thanks for the suggestions, a lot of good ideas.
Yes Leith, much of what I have learnt is from the recorder, but I am also learning the recorder's way isnt always the best.
I've stripped down the code and am now left with this one section which still takes around 30 seconds to run. It starts at the top of the sheet and if it find a 1, its copies the row to another sheet. If it finds a 2, it should do nothing :-
Any ideas if this can be done faster? ThanksPlease Login or Register to view this content.
Last edited by bd528; 09-12-2010 at 08:32 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks