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

  • Thread starter Thread starter Stefano
  • Start date Start date
S

Stefano

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
 
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
 
Back
Top