Macro freezing worksheet

[tarmand]

I have about ten worksheets that I update every month. They are all the same but for different projects. They all have the same 7 macros in them. They have worked fine for about a year, but now when I run one of the macros, it freezes Excel for about 15-20 minutes. When I look at the task manager, it shows that Excel is running so I just wait and it always eventually unfreezes. Any ideas as to what is wrong? This happens when I run that macro on all of the worksheets.

 

[Peter_SSs]

tarmand

Would it be possible to post the macro code on the board?

 

[tarmand]

Here it is:

Sub Datechange()
Range("$b$2:$b$65536").Select
For Each c In Selection
c.Value = Right(c.Value, 4) & "-" & Mid(c.Value, 2, 2)
Next c
End Sub

 

[GlennUK]

Hi tarmand,

can I ask why it's necessary to fill values in column B for so many cells? Surely the required amount of entries is going to be less than 65,535 cells worth, isn't it?

When doing a loop like this you may wish to switch off calculation before processing and reinstate it afterwards. See the Application.Calculation command in VBA help.

 

[tarmand]

Glenn,

I just did it to the end of the worksheet since I never know how many rows I will have. It is usually less than 10,000 rows so I could change it to that. Do you think that would help? What confuses me is that it has always worked in a matter of seconds in the past and just in the last two days has begun to give me problems.

Thanks,
Tammie

 

[Cbrine]

Give this code change a try. It's dynamic and knows what the last populated cell in Column B is, so you don't do the entire column.

Sub Datechange() 
Dim c as range

For Each c In range("B2",range("B"&activesheet.rows.count).end(xlup))
c.Value = Right(c.Value, 4) & "-" & Mid(c.Value, 2, 2) 
Next c 
End Sub

As far as what is cause the delay now, did you add new formula's, maybe a filter? These kind of things may cause a slow down.

HTH
Cal

 

[tarmand]

Wow, that worked. It ran the macro in less than a second. I don't think I changed anything in the reports, but others do have access to the reports and I don't know what they may have done.

Thanks!