I have an add-in that let's users manipulate potentially large sets of data.
After certain operations, my code will autofit the column widths of a
variable number of columns with code like:

range("A9:C9").EntireColumn.AutoFit

I've never had any problems with this in the past.

However one user has created a situation where the above operation never
completes (Task Manager shows Excel using 99% of the cpu until we kill the
process).

The worksheet being AutoFitted has 32,545 rows. So big but not gigantic.
I've ensured that there are no merged cells in the range before trying the
AutoFit. I've also tried doing the AutoFit one column at a time (e.g.,
range("A9").EntireColumn.AutoFit) but that loops too on the first column.

So far the only two versions of Excel I've been able to test with are Excel
2002 and Excel 2002 SP3.

Any good ideas out there?

TIA,

josh

Is this inside a worksheet event (worksheet_calculate)

application.enableevents = false
range("A9:C9").EntireColumn.AutoFit
application.enableevents = true

Maybe...

Josh Sale wrote:

I have an add-in that let's users manipulate potentially large sets of data.
After certain operations, my code will autofit the column widths of a
variable number of columns with code like:

range("A9:C9").EntireColumn.AutoFit

I've never had any problems with this in the past.

However one user has created a situation where the above operation never
completes (Task Manager shows Excel using 99% of the cpu until we kill the
process).

The worksheet being AutoFitted has 32,545 rows. So big but not gigantic.
I've ensured that there are no merged cells in the range before trying the
AutoFit. I've also tried doing the AutoFit one column at a time (e.g.,
range("A9").EntireColumn.AutoFit) but that loops too on the first column.

So far the only two versions of Excel I've been able to test with are Excel
2002 and Excel 2002 SP3.

Any good ideas out there?

TIA,

josh

--

Dave Peterson

Dave,

Well you're right about the AutoFit being called from an event handler. In
my case its a CommandButton_MouseUp event handler.

I tried bracketing the AutoFit with the disabling and enabling of events as
you suggested but it didn't cure the problem ... Excel still loops.

I liked the direction I think you were going so I commented out the
AutoFit and let the event handler run to completion. I then launched the
VBE and tried executing "Range("A1").EntireColumn.AutoFit" from the
immediate window and it to went into a loop.

Then I thought why don't I remove the VBA code from the equation. After
manipulating the data with the AutoFit code commented out, I tried
performing the AutoFit operation from Excel (i.e., I double-clicked on the
column seperator) that too went into a loop.

At this point I went back, recreated the data with the AutoFitting commented
out and saved the workbook. I reopend the workbook on that same machine and
attempts to AutoFit from Excel continue to fail.

I brought the saved workbook back to my machine and opened it in both Excel
97 and Excel 2003 SP2 and it won't AutoFit there either.

Now I'm all out of ideas again. Any more suggestions?

Thanks,

josh


"Dave Peterson" wrote in message
...
Is this inside a worksheet event (worksheet_calculate)

application.enableevents = false
range("A9:C9").EntireColumn.AutoFit
application.enableevents = true

Maybe...

No, I don't.

Well, maybe your code has other spots that turn events on/off???

(but that ain't much of a hint.)

Josh Sale wrote:

Dave,

Well you're right about the AutoFit being called from an event handler. In
my case its a CommandButton_MouseUp event handler.

I tried bracketing the AutoFit with the disabling and enabling of events as
you suggested but it didn't cure the problem ... Excel still loops.

I liked the direction I think you were going so I commented out the
AutoFit and let the event handler run to completion. I then launched the
VBE and tried executing "Range("A1").EntireColumn.AutoFit" from the
immediate window and it to went into a loop.

Then I thought why don't I remove the VBA code from the equation. After
manipulating the data with the AutoFit code commented out, I tried
performing the AutoFit operation from Excel (i.e., I double-clicked on the
column seperator) that too went into a loop.

At this point I went back, recreated the data with the AutoFitting commented
out and saved the workbook. I reopend the workbook on that same machine and
attempts to AutoFit from Excel continue to fail.

I brought the saved workbook back to my machine and opened it in both Excel
97 and Excel 2003 SP2 and it won't AutoFit there either.

Now I'm all out of ideas again. Any more suggestions?

Thanks,

josh

"Dave Peterson" wrote in message
...
Is this inside a worksheet event (worksheet_calculate)

application.enableevents = false
range("A9:C9").EntireColumn.AutoFit
application.enableevents = true

Maybe...

--

Dave Peterson

Dave,

It turns out that the AutoFit eventually completed given enough time and a
fast enough machine. I've posted a new problem with a description of
"AutoFit Very, Very Slow".

Thanks for your suggestions,

josh