Macro Taking Forever to Complete

[hk4kim]

Attached is the list of files that are used to run the BacklogReport file.
I've been running the file in xls format, but recently the filesize increased immensely in one day( 800kb to 1.8mb) and it took forever to even open the file.

So converting the file to xlsm format seemed to have reduced the file size as well as the file loading time.

But today, when pressing update button in BacklogReport, it runs the macro but runs EXTREMELY slow.
It used to update reports within a minute but this one takes more than an hour.

Any help would be appreciated.

p.s) password is veronica

Thanks for your help

[tigeravatar]

hk4kim,

Uhhh.. there's a lot of code there, and I can see several inefficiencies, but how about you let me know what the code is intended to do and maybe we can come up with something better.

[hk4kim]

Basically, the code lets the information in datafile 1 and datafile 2 to be uploaded into Backlogreport. Then it chekcs for whether there has been duplicates or whether the added data is new. If the data is new then it gets added to the bottom list. If data is considered duplicates then it updates the existing data. If the data is no longer there then the information gets transferred to the shipped data workbook. That's pretty much the whole thing. If you look at the code you can see what each codes does in a brief explanation.

The problem is, it was running fine even with the inefficiency but now its running extremely slow all of a sudden.
I was thinking this has to be associated with the tremendous increase in file size although it should not increase that much with small additional data included into the update.

[tigeravatar]

hk4kim,

Give this version a try, it completed for me in about 30 seconds:

Please Login or Register  to view this content.

[hk4kim]

Please Login or Register  to view this content.

getting compile error:variable not defined in dStart = Timer

[tigeravatar]

That line was for timing/testing. You can delete that line.

[hk4kim]

when I run the code, the spreadsheet freezes with the hourglass icon and in about 3minutes it does update but when it creates the newly updated version it created this, which isnt what i want. I want the Backlogreport to be updated with the current date

[tigeravatar]

Oops, forgot to change it away from activeworkbook, update this line:

Please Login or Register  to view this content.

to be this instead:

Please Login or Register  to view this content.

[hk4kim]

Ok thanks!

I'll run it again tomorrow morning and let you know how that turns out.
One mor thing, when i run the macro, it usually runs in a way that I can instantly see which cells get updated but with this code it freezes for a few minutes and then updates
the whole thing. Does this have to do with my computer?(Excel2010)

[tigeravatar]

No, it's because seeing each individual change greatly slows down code execution. So I moved all of the changes to be in memory and then output the changes at the end all at once.

[hk4kim]

One more note, even simple operations like deleting an entire row or column takes about a minute to proceed.
Would you be able to figure out why this is happening?

[hk4kim]

Also, I've noticed in the code that the operations for ws2 is left out.( most operations have only ws1 included)
I'm not too sure if this was done on purpose but I want the same operations for ws1 and ws2 to be performed

Thank you very much!

[tigeravatar]

I would copy the contents of the main workbook into a blank new workbook and try it there. It's possible some part of it got corrupted.

[hk4kim]

Please Login or Register  to view this content.

I was hinted that EntireRow.Delete is very slow and to fix this filtering the info with autofilter and then delete the range of values filtered was a possible approach to resolve this. How would you be able to do that here?

[tigeravatar]

It can be slow when you're doing it one line at a time. Again, it's not being done one line at a time, it's being done all at once (just like a filter would do).

[hk4kim]

Ah I get what you are saying now.
Does that mean adding manual calculation command in the beggining won't do anything?

[hk4kim]

Please Login or Register  to view this content.

If i'm not mistaking, the calculations are based on ws1 only. I want the same operations to be performed for both ws1 and ws2. Just wondering if this was done on purpose or not...

[tigeravatar]

oh right, in the If bUpdated = False section, remove the 1 so that it's just ws.Cells....

[hk4kim]

everything works fine, only problem is when i update it pivot table diasppears.

compare the 18th to the 19th.

Thank you once again tigre! I really appreciate your help

[tigeravatar]

hk4kim,

I can't find a pivot table in either of files. Where is it supposed to be located?

[hk4kim]

tigeravatar,

Row 6. To be more specific, its more a of a filtering function that compiles ths lists below and allows me to sort.
The little inverted pyramid symbol which allows me to do that is gone if you look at the july 19 file.

[hk4kim]

It's a very very minor issue which can be fixed manually but I just wanted to know if I have to do this everytime

[tigeravatar]

oooh, the autofilter! You can delete the autofilter line in the code, that should take care of it. I think it's actually turning it off instead of on. Or put it in a second time, at the top of the code to remove it before hand so that the one near the bottom of the code will put it back on.

[hk4kim]

tigeravatar,

so far working as intended, with one minor issue.
If you look at the second workbook(shipped data), the newly updated cells(beginning at cell 617) should all be shifted one cell to the right. Let me know if anything is unclear.

Thank you so much for helping me!