i think it may be this, but i'm sure someone who knows more than me will answer
:

Data table calculations Data tables recalculate whenever a worksheet is
recalculated, even if they have not changed. To speed up calculation of a
worksheet that contains a data table, you can change the Calculation options to
automatically recalculate the worksheet but not data tables.


--


Gary


"Sandy" wrote in message
...
Gary
My apologies I didn't see the end part - with that in it works very well.
Thank you

Curiosity - What does the "xlCalculationSemiAutomatic" do.

Thanks again
Sandy

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
did you see the end of your code that i pasted? it turns calc on after your
code runs.

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

--


Gary


"Sandy" wrote in message
...
Gary
It certainly speeds up the code execution but it prevents all other sheet
calculations!
Is there another way without interfering with the cell formulae?
Sandy

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
don't know the size of your work book, but try adding this

Calculation = xlCalculationManual

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
For Each mycell In Range("C11:K11,M11:U11")
If mycell.Value = "" Then
mycell.Offset(32).Value = ""
ElseIf mycell.Value = 0 Then
mycell.Offset(32).Value = 0
End If
Next
MsgBox "End"

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub

--


Gary


"Sandy" wrote in message
...
Why should the following (supposedly simple piece of) code run so slowly:-

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
Application.EnableEvents = False

For Each mycell In Range("C11:K11,M11:U11")
If mycell.Value = "" Then
mycell.Offset(32).Value = ""
ElseIf mycell.Value = 0 Then
mycell.Offset(32).Value = 0
End If
Next
MsgBox "End"

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

Thanks
Sandy