VB Excel Copy/Paste with macros


I run macro's that along with clearing a large amount of data from cells it also copies and pastes a large amount of data from one spreadsheet into another spreadsheet where the data was just cleared.  I know that when you are doing this manually that you would be able to use undo and reverse many of these actions.  It seems to slow the execution of the macro's down.  Is there a way to disable the undo/redo function at the beginning of the macro execution and then starting it back up in order to speed up the performance?  

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Macros do not support undo unless you code them to support it (which is rare.)

If you are having performance issues, I've found it's often because the macro recorder is not optimal in how it handles things and by learning a few techniques, you can improve performance tremendously.
For example, I recorded a macro yesterday to highlight even numbered rows from 2 to 1000 (well, I tweaked it to go through a loop) and it took about 2 seconds.
I optimized it to not do a ".Select" each time and to directly highlight the rows and I was able to get to row 5000 in about 1/2 second so that would be about a 20x speed improvement just by replacing two lines of code (and understanding where the slowdown was occurring and what some alternatives were.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.