Code that sometimes works and sometimes doesn't

Hi Guys

I wonder if someone could help me. Code below is only part of my macro. The problem is that it sometimes works and it sometimes doesn't. Sometimes it will freeze and it will not save the changes, sometimes it will not print. After i press X to close excel sometimes it takes 3-5min to close. I would be grateful for tips how to improve this code to run smooth and without errors.

Please Login or Register  to view this content.

Hi,

this might only be part of why it doesn't always work, but when you are assigning values to variables here

Please Login or Register  to view this content.

you don't reference the full object model. This means that excel will assign a range to the variable from the active sheet. And I assume that can vary from time to time. Try using it like this

Please Login or Register  to view this content.

like you did with your other variables.


edit: also, one more thing. If you declare variables like this

Please Login or Register  to view this content.

then only r6 will be a range, r...r5 will be variant. You can use

Please Login or Register  to view this content.

I've got another thing to add. It is redundant to reference a range (and its property) like this:

Please Login or Register  to view this content.

Instead of Range(cell3.Address), you can just use cell3, because it is already a range. Also, ThisWorkbook is given, since it's an event macro so it's obvious that you're referencing the workbook you store the code in.

Please Login or Register  to view this content.

This line does the same.