Macro deletes formula

[Guest]

I have created a macro to restore the row height in a protected worksheet
when text in a cell of the parent worksheet is deleted but when I run the
macro it deletes the formula in the cell. The macro is:
Sub Autoheight()
'
' Autoheight Macro
' Macro recorded 12/10/2004
' Keyboard Shortcut: Ctrl D
' Dim myCell As Range

For Each myCell In Selection
myCell.Value = AutoFit
Next myCell
End Sub
What changes do I need to make to the macro prevent the formula in the cell
being deleted?

 

[Don Guillett]

try deleting the

..value=
so you have mycell.autofit

 

[Guest]

Don
Thanks. Tried this but got runtime error 1004
Autofit method of Range class failed

What other solution might there be?

 

[Don Guillett]

use this instead

mycell.autofit
mycell.entirerow.autofit

 

[Gord Dibben]

I don't believe cells have an Autofit Property.

Try Selection.Rows.Autofit

Sub Autoheight()
'
' Autoheight Macro
' Macro recorded 12/10/2004
' Keyboard Shortcut: Ctrl D
' Dim myCell As Range

Selection.Rows.AutoFit
End Sub

Gord Dibben Excel MVP