Macro has long runningtime

[madsklavsen]

Hello Everyone.

I am using the following macro to add information of active assignments to a new sheet. HOWEVER the macro takes more then 15 seconds to run. Is there any way of shortening this?


I appreciate any help. Thanks in Advance

Cheers,

Mads

Please Login or Register  to view this content.

[millz]

How about this?

Please Login or Register  to view this content.

[madsklavsen]

That's perfect Millz! Thanks a lot! (only 2-3 seconds)
The macro is a part of multiple macros i run at the same time.
I also have this macro which takes about 10 seconds. Any way of shortening this?

Thanks in advance
Cheers,

Please Login or Register  to view this content.

[millz]

You may try the remove duplicates function in Excel itself. If this doesn't work then let's modify your existing macro.

Please Login or Register  to view this content.

[madsklavsen]

Hi Millz,

That option doesn't seem to work.

If you have any other solution please let me know
Cheers

[millz]

Were there any errors?

How many rows of data is (usually) involved in the removal of duplicates?

Would the number of rows ever exceed 32000?

[madsklavsen]

I'm not sure how to use the new line of code you gave me.

The data looks like this and is typically 100-400 rows
Data.PNG

[madsklavsen]

This is the error i get:
Error.PNG

[xlnitwit]

Hi,

It would likely be considerably faster to use an array rather than reading each cell and further to delete all relevant rows at once.

Please Login or Register  to view this content.

[millz]

This is the error i get:
Attachment 470439

The error is because you have entered the code wrongly.

Try this, just copy and paste the whole code:

Please Login or Register  to view this content.

[madsklavsen]

Okay thanks.

However, i find that this is just a simple "remove duplicates" formula.
What the beforestated macro does is delete the row of the duplicate AND delete the first appearance of a duplicate instead of the last. Both of these features are vital.

Please let me know if you have any ideas. Thanks!

[millz]

I see, did not know that there's a requirement to keep only the last duplicate record.

This solution replaces with values (no deletion of rows), so it could be faster:

Please Login or Register  to view this content.

[xlnitwit]

What the beforestated macro does is delete the row of the duplicate AND delete the first appearance of a duplicate instead of the last. Both of these features are vital.

Please let me know if you have any ideas. Thanks!

I have corrected the suggestion I made earlier to loop backwards.