Rows().AutoFit starts a calculation, but Columns().AutoFit doesn't
 

This example copied from the help of the WorkSheet.Calculate event works well:
Private Sub Worksheet_Calculate()
Columns("A:F").AutoFit
End Sub

But it loops forever when I replace it with:
Private Sub Worksheet_Calculate()
Rows("10:20").AutoFit
End Sub

It looks like resizing the rows (even rows outside the UsedRange) causes the
volatile functions to be calculated, while resizing the columns doesn't.

Does it make sense?
How can I resize the rows after a calculation?

Thanks,
Stefano

Rows().AutoFit starts a calculation, but Columns().AutoFit doe
 

I just found out that disabling the events doesn't loop forever, but a second
calculation still restarts. It doesn't finish, and the result that a lot of
cells have the wrong values.

If I set a breakpoint on a user function, I see that the correct value is
displayed in the cell, then the second calculation starts and:
- with EnableEvents = False it replaces many values with zeros
- with EnableEvents = True it loops forever

I wasn't able to find a way to use the Row().AutoFit in the Calculate event,
even if I could use the Column().AutoFit

Sadness,
Stefano

"Mike H" wrote:

Hi,

I can't replicate your problem but this should stop recursive calls

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Rows("10:20").AutoFit
Application.EnableEvents = True
End Sub

Mike

"Stefano" wrote:

This example copied from the help of the WorkSheet.Calculate event works well:
Private Sub Worksheet_Calculate()
Columns("A:F").AutoFit
End Sub

But it loops forever when I replace it with:
Private Sub Worksheet_Calculate()
Rows("10:20").AutoFit
End Sub

It looks like resizing the rows (even rows outside the UsedRange) causes the
volatile functions to be calculated, while resizing the columns doesn't.

Does it make sense?
How can I resize the rows after a calculation?

Thanks,
Stefano