Very Slow Macro - Can this be more efficient?

Here are two things I would recommend.

[1] Turn the screen updating feature off at the beginning of your code. In particular, include this immediately after your "Sub Rota()" line: Application.ScreenUpdating = False. This will keep your screen from flickering (as Excel is updating the screen for each action that you are taking). Then right before the "End Sub" line, add the following: Application.ScreenUpdating = True.

[2] When copying and pasting, it is not necessary to apply the .Select method. You do need to select a cell if you are working manually in Excel, but in VBA, you can skip having to select the cells you wish to copy elsewhere. For example, if I want to copy and paste value the contents of Cell A1 into Cell B1, then I could do it as follows:

Please Login or Register  to view this content.

Another way to do this, if you do not want VBA to copy and paste, is to simply assign the value in Cell A1 to Cell B1:

Please Login or Register  to view this content.

Hope this helps.

Thanks Dimitrov

Done as suggested with regards to the Screen Updating, but it doesn't seem to have improved the performance - it is taking around 10 minutes to populate 160 cells!

Attached is the whole spreadsheet which hopefully makes the objective a little clearer.

Is there any way this can be improved to make it quicker, or is there a completely different approach?

Thanks

TG

I don't know what the code does but when I ran the macro included in your sample with a few changes it took less than a minute.

Please Login or Register  to view this content.

BTW the changes I made were the same as suggested earlier.