Optimize code copy very slow

[JohnGreen2]

I have a macro thats been written by the recorder but its exceptionally slow. What can I do speed up the macro. It copies data from range R2-R130 to A23-C400 and call another macro. If I run the code it goes not responding and it takes almost 12 min to run through (:

Thank you for any pointers and help

Please Login or Register  to view this content.

[TMS]

The last part could be tidied up to this. You don't normally need to select a cell/range to operate on it but I don't know what the other macros require.

Oops ... Edit: forgot the code example ...

Please Login or Register  to view this content.

[TMS]

Note: you don't need any of the scrolling. And you can combine rows that end with .Select followed by Selection.

[Richard Buttrey]

It looks like a macro captured via the recorder.

The recorder is great for capturing code but it should always be edited and modified to clean it up.

As Trevor says in general .Select is not necessary. Why not upload the workbook and tell us what you want to achieve. It's almost certain that your code is not the most efficient or elegant way of getting to the end goal.

[TMS]

I'm guessing you change a cell value, then run a macro to do some calculations, and then store the outcome somewhere.

Very likely that can be put into a loop.

We would need to see the rest of the code to determine how to improve the code effectively.

[JohnGreen2]

The last part could be tidied up to this. You don't normally need to select a cell/range to operate on it but I don't know what the other macros require.

Oops ... Edit: forgot the code example ...

Please Login or Register  to view this content.

Thank you will that copy copy it to the clipbord ? I made changes took out all the scrolls and select but its still terribly slow.Will try your copy and report back thank you

P>S I have a macro with VBA collections and formulas. If mycopy ranges are 30 the macro is fast but as soon as I am copying 300 lines thats when its slow and not responding.

[JohnGreen2]

It looks like a macro captured via the recorder.

The recorder is great for capturing code but it should always be edited and modified to clean it up.

As Trevor says in general .Select is not necessary. Why not upload the workbook and tell us what you want to achieve. It's almost certain that your code is not the most efficient or elegant way of getting to the end goal.

Correct Richard

[TMS]

Suggest you post a sample workbook with ALL the code and sufficient typical test data to demonstrate the slowness. Ideally, anonymise a real example.

[pella88]

As stated by couple of people already: recorder is a good first step.. However, recorder records all small things which are usually unnecessary in codes... In your case, possibly loop can be used (if there is any logic behind locations of copy and pastes) + all selection, scrolling, activation can be removed... One thing which can speed up the process a bit is adding Application.ScreenUpdating = False at the beginning of you code and Application.ScreenUpdating = True at the end of your code, but this is not complete solution...

I agree with TMS, without seeing an example, it is hard to assess how to properly do it...

[TMS]

Both these versions are untested as the other subroutines are not present. Test on a copy of the workbook!


"Tidy version"

Please Login or Register  to view this content.

"Short version" with loop

Please Login or Register  to view this content.

[JohnGreen2]

Both these versions are untested as the other subroutines are not present. Test on a copy of the workbook!


"Tidy version"

Please Login or Register  to view this content.

"Short version" with loop

Please Login or Register  to view this content.

Thank you TMS for the code...........I finally optimized the code that took 12 Seconds and it take 5 seconds now.I have used some of your code thank you for the help TMS. I might add there was a user error involved here -My Macro have read values it should not have read...I manage to find the culprit by braking the macro down in pieces. Thank you TMS and Richard for your help

JohnGreen2

[JohnGreen2]

As stated by couple of people already: recorder is a good first step.. However, recorder records all small things which are usually unnecessary in codes... In your case, possibly loop can be used (if there is any logic behind locations of copy and pastes) + all selection, scrolling, activation can be removed... One thing which can speed up the process a bit is adding Application.ScreenUpdating = False at the beginning of you code and Application.ScreenUpdating = True at the end of your code, but this is not complete solution...

I agree with TMS, without seeing an example, it is hard to assess how to properly do it...

Thank you I mange to solve it....was more a error as the macro read the wrong code. I moved the procedure and the code works flawlessly now -Thank you

[TMS]

You're welcome. Thanks for the rep.


For future reference, please do not quote whole posts; it just clutters the thread. If there is more than one reply, it is useful to quote specific relevant extracts which you are responding to or expanding on.