Speed up slow macro loop

The macro below does two things: it concatenates the values in Column E, and adds the values in column J, for any duplicates in column B . The sheet it works on has 2246 lines, so the code should operate quickly. However, it takes a little under three minutes to complete (2:58). I'd appreciate some help in speeding it up.

Please Login or Register  to view this content.

Post the workbook.

not the fastest option, but you can try

Please Login or Register  to view this content.

Bob,
I've attached the workbook.

Nilem,
I got an Overflow error at this line:

Please Login or Register  to view this content.

It looks like you are appending quantities and adding dates, that doesn't sound right.

Can you give a text description of what you are trying to do, without indicating the columns?

Also, generically, interacting between VB and the Excel GUI is very slow, I would recommend loading the data into an array, doing your operations there, then pasting the results back to Excel all at once. You could have an array to hold your WPR number, Quantities and Due Dates, calculate and adjust as necessary, paste the Quantity and Due Date arrays back, then delete the extra rows. In your calculate section, you could index sort based on WPR number, which would speed up your searches considerably.

Reply on my first two questions, I'll see what I can do.

Sure, here you go:

for any duplicates in WPR_Number I want to A: Concatnenate the line descriptions and B) add the Total costs (sorry, I said J earlier, and meant I), then delete the other duplicate rows. So, in the end, I shouldn't have ANY duplicate WPR_Numbers, but the line description column should have ALL of the line descriptions for that WPR in one cell, and the Total Cost should be the sum of the individual line items for that WPR.

I'm not that conversant with Arrays, hence my problem today. Any help is greatly appreciated.

Nilem,
I owe you an apology: My original code made use of an extra column which moved all my columns over by 1. I tried your code on my original data, minus that column, which is why I got the overflow on your code. Adjusting from columns 6 and 10 to 5 and 9, your code works flawlessly, in 104 seconds. Still slower than I'd like, but much faster than my original code.

or

Please Login or Register  to view this content.

Holy Smokes, Nilem! Your last code just ran in .38 minutes! That's pretty fast. I'm testing it now with my main data and will let you know the results when completed. Thank You!

Nilem,

Your code checks out. It runs without a hitch, and it's very fast! Would you like to help me work on my car next?

Thanks so much. I couldn't have done it without you.

Walleye,

Sorry to tell you, but Nilem's code is faster. I didn't get a chance to test both yesterday, so did just now. Using the same timer setup, Nilem's code runs in .44 seconds, yours runs in 1.16. Not a big difference between the two; they're both much faster than I had any right to expect. Thanks for showing me a different way to approach the problem.

No need to be sorry, I'll take a look at his code and try to learn from it.

You and me both.