Hi all,
My code works great so i shouldn't really complain, but it is a little slow for a short macro(30 seconds). The code has been compiled using macro recorder and help from excel forum users (Thanks all!). I was just wondering if there were any ways to speed it up.
Thanks for any help
Tom
Please Login or Register to view this content.
Last edited by oo0tommyk0oo; 06-21-2011 at 03:35 PM.
Hi
Select and then using Selection takes longer than the below. Change the equivalant lines to the below and see how much time you save.
Please Login or Register to view this content.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
All those Selections aren't necessary & will slow the code. Page setting in code can be slow
Hope that helps.
RoyUK
--------
For Excel Tips & Solutions, free examples and tutorials why not check out my web site
Free DataBaseForm example
I don't know if everything you have is necessary in your code but I removed all of the .Select statements out:.Select is always shown in a recorded macro but is not usually necessary when writing code.Please Login or Register to view this content.
Regards
If you're happy with someone's help, click that little star at the bottom left of their post to give them Reps.
---Keep on Coding in the Free World---
I just lost the game! Wow MarvinP, I bow in your presence!
Hey Mordred,
You beat me to the punch most of the time. I got you by 10 minutes on this one.
Thanks to everyone for the quick response will give the code a try now.
Works like a charm, takes about 10 seconds now. I'm guessing that is a normal time for changing all those column widths and borders.
Thanks for all the Help!
Quick update, i missed some of the updated code from Mordred, when i combined it with the code from MarvinP, it now runs in 3 seconds!
Cheers!
If you use an empty preformatted sheet, you can use it to format other sheets:
Please Login or Register to view this content.
I didn't think of that.. the sheet i am copying from is identical in formatting, I'm simply removing all the formulas to values, so my coworkers can play with it without deleting anything important (they complain when i lock things as they like to edit everything)
That would shorten the code to much though that when i showed my boss, she would not be so impressed!
Will remember that for the future though!
Hahahaha. Does your boss actually want to see your code? I know that my boss's eyes glaze over when I have showed her code. LoL.Originally Posted by oo0tommyk0oo
Do you need to set each one of those properties in .PageSetup? I know the recorder places them there, but most of the time, you only need the properties you are actually changing. If the default is fine, you don't need to include them.
I am talking about all of this:
If all of that is set beforehand and is not changing, then you can omit all of that.Please Login or Register to view this content.
It won't make your code all that much faster, but it will make it easier to read and edit.
Why not do something like:
Please Login or Register to view this content.
@ Mordred - No she glazes over as well, but i think it is important to show her every so often, so she knows how long it takes to keep changing it....
@ Whizbang - That codes works great, and as the original source (with formulas) has a macro attached to it which resets all the page settings to how i want them, there is not need to do the same with the Version copy.
Thanks everyone for the help
@Whizbang
In that case I'd prefer:
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