Loops are too Slow.

Hiya

I don't know how much help I can be as I can't actually open the file for your code, but from experience I found that sometimes use the macro to take a copy of your list and delete those cases you don't need so that you are looping through a much shorter overall list?

Hope this is of some help to you

TMShucks,
Thank you so much. Your advice makes a lot of sense and I thank you. I am unsure how to proceed due to a confidentiality agreement. This is my first time asking for help so I was unfamiliar with the protocol and you helped.

Having documented this, I have formulated a different approach that I could not see when I started. I think I can create an additional formula column that lists the number of months in the range, so that (if it works correctly), I don't loop for each month, but only once (on the monthly loop). Thus dropping the overall loops to 1 for reading and 3 for displaying. I will update my results.

LouiseH24,
Thank you for your assistance. I have posted an update to TMShucks which is relevant. I had deleted everything except what was going to be displayed, it was only determining which months they would display. Having written this out and thinking further, I believe that I have a possible solution. I will update the results.

I appreciate your feedback.

I am still reviewing the whole project for improving the speed, but I did help a lot. I improved it from ~38 seconds to ~12 seconds (but increased additional code to get it back up to 20 seconds).

I was looping through 38 records (Projects) and comparing and placing it on a display that went out to 2016 in my test. The display started with 25 records and ended around 33 records.

Old Method
1.) I would loop, for each Project, through every Project listed on the Display each month to determine if it matched the prior month Project. If it did, I updated the Display for that month and put a flag on that Project for that month. (The flag would prevent that Project from being examined in following loops for that month.)

2.) If there was not a match, I would loop, for each Project that was not flagged, through every Project listed on the Display each month to determine if the Project's Department had an empty cell for that month. If there was a match, I placed it in the first empty cell for that Department and put a flag on that Project for that month.

3.) If the Project's Department did not have any empty cells for that month, I would loop, for each Project that was not flagged, through every Project listed on the Display each month to find the last record in the Display for that Department. I would then insert a record into that Department and copy the Project into the empty cell that was just created for that month. I would then put a flag on that Project for that month.

4.) If the Project's Department did not exist in the current Display, I would create a record at the bottom of the Display to create a new Department. I would then insert a record into that Department and copy the Project into the empty cell that was just created for that month.

5.) The flags would be removed and we would start all over for the next month.

New Method
***) I completed more calculations in hidden columns to determine 1) how many months, from the current month, until the Project Started, and 2) how many months would the Project continue from that start month.

1.) I then followed all four steps above. I changed the fact that I was updating a flag and I only completed each set of loops once instead of for every month.

This saved me about 20 seconds for a relatively small number of records being examined.

I am not sure if this is beneficial to anyone else, but since I asked the question, the least I could do was post my solution.

Thanks for the assistance.

TMShucks,
Thank you for the kind offer. I will take you up on it, but right now my daughter is graduating and family is coming in from all over. I will provide a cleansed copy on Sunday or Monday. Enjoy your weekend.