Very Slow code

[Sandy]

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

 

[Gary Keramidas]

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

 

[Don Guillett]

Might?? even be simpler (depending on data)

For Each mycell In Range("C11:K11,M11:U11")
mycell.Offset(32).Value = mycell.Value Next

 

[Sandy]

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]

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

 

[Sandy]

Don
Sadly that won't work. Let me explain a bit more.
If the value in ("C11:K11,M11:U11") is 0 then the value in corresponding Row
43 cell (Offset(32)) must be 0 however if it is >0 (integers) then the value
(in Row 43) is input by the user and can be anywhere from 1 to approx 60.
The values in (C43:K43,M43:U43") are used in other calculations, and
conditional formatting is included.
Thing is though if I use the same code in an otherwise blank worksheet, ie
no calculating cells or conditional formatting it still runs slowly?!?
Sandy

 

[Sandy]

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]

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.