Excel Freezes and Crashes During Do Until Loop If Ran More Than Once

I set up a vba macro a couple of years ago that formats a report pulled out of OneSoure, and it has worked well for me, but if the myself or another user runs it more than once while in the same instance of Excel, Excel freezes and gives the "Excel is not responding" message and I have to close it in task manager. You can save the file and excel after running it once and then when you reopen it you can run the macro again with no issues (unless again you try running it a second time). I have tried using F8 to go line by line to find the exact spot where it crashes and it works for me when I do that, but the minute I try to run it, it freezes up in the Do Until Loop, I haven't been able to pinpoint exactly, but I believe at the Selection.Columns.AutoFit line.

Full disclosure, this is sub that is called by another sub. I have attached a copy of the report the code below would be used to format, each report typically has 8-16 tabs that would be processed by one of 5 subs. When I run the macro, it takes about 50 seconds to process the full workbook (5 seconds to actually execute the code and the remaining 45 seconds is the time it takes to make the changes to page setup.

Does anyone have any thoughts on how I can fix my loop? (btw, I use Excel 2013 and my coworkers use Office 365)

Couldn't get the code to populate in the original post. Here it is:

Please Login or Register  to view this content.

Originally Posted by bwink88

. . . the code below . . .

What code?

You'd need to show the code causing the problem.

A guess: the 1st time you run it, you reach a bounding cell in fewer than 1,000 iterations. The 2nd time you run it, the macro tries to iterate slowly through all other cells in a given worksheet.

You could add something like this around your loop.

Please Login or Register  to view this content.

This isn't a fix, just a debugging aid.

I'd suggest replacing your problem loop with

Please Login or Register  to view this content.

Better to avoid Select and Selection whenever possible. This approach sets ulrc the botton-right cell of UsedRange in the active worksheet. That may go further than you need, but if this doesn't hang Excel, we can work around that in another phase.

The idea is to set rng to the entire range from C8 to urlc, then apply column width autofit to that entire range in a single operation, then iterate through the columns ensuring the minimum column width is 15.71.

This also checks whether UsedRange exceeds 100 columns, which may indicate a problem. Change as needed, but it's unlikely it should exceed 1,000 columns.

This also checks whether UsedRange doesn't span col C, in which case you should debug things.

hrlngrv, thank you for your help, and I appreciate your suggestion to stop using select/selection, I will make sure to make that a standard practice. I went through and implemented your suggestions and while it ran faster the first time around, it still bogs way down the second time through. I was able to get it to run the second time, but it took half an hour to process seven worksheets. If I save that file and then re-open it and run it, it goes back to taking the thirty seconds it took to run it the initial time.

I put a ' at the beginning of the for loops to play everything but the loops and tried running it again and it still ran really slow. If I F8 through the add-in, it runs faster than if I click run sub. I put a ' in front of the call to "StateProvisionFormat" to see if that part had slowed and it ran at a normal speed. When I F8 through the StateProvisionFormat sub, it seems to take longer on any code relating to resizing or hiding the column or row.

Without removing any functions, I tried the add-in on my girlfriend's laptop (she has the Office 365 Excel app), and the same report that took 50 seconds to process on my work laptop took 18 seconds (I imagine mostly because the page setup feature bogs my work laptop down due to it being connected to a network printer) and when I ran it again, it slowed down to 44 seconds.

When reading other forum posts about people having issues with a macro crashing through the second run, people had cited issues with the install of

Metaldrgn, thank you for your suggestion. I tried this out and was still having the issue. I hadn't used .CurrentRegion before, I will have to keep that on in mind in the future. I need to learn how to use arrays, it is on my to do list. My add-in isn't moving any data though, it is just formatting a report that I download from our online tax software. The issue seems to be when I manipulate the size or visibility of the rows/columns.

Fixing problems like this requires isolating the primary cause. My debugging suggestions were in that vein. Not solutions, but ways to isolate the primary cause.

If the slowdown occurs during changing column widths, maybe try something brute force: come up with some string composed of underscores, minus signs, periods and spaces which produce your desired minimum column width. Make that a string constant in your VBA module. If row 5, say, from col C right should always be blank, enter that string as the value for C5:XXX5 (where XXX is a placeholder), then include up to row 5 in the AutoFit call. That'd ensure the minimum column width would be what you want without needing to use a subsequent loop. After the AutoFit call, clear C5:XXX5.

Also consider adding the following at the top and bottom of your macro.

Please Login or Register  to view this content.