Hi all,

I have a spreadsheet with about 20 columns of data, 3 of which are
long-winded array formulas. I have some conditional formats and simple IF
functions in the other 17 columns. I would like to keep the sheet calc to
auto, but this makes entry slow because of the 3 columns of arrays. Is there
a way to exclude the array columns so I can keep the auto calc?

Thanks in advance,

SPL

VBA can calculate just a single range. Something like:

Sub CalcRange
Sheets("Data").Range("E:F").Calculate
End Sub

You can have calculation option to Manual and use a button to call this
procedure.

HTH
Kostis Vezerides

Thank you
"vezerid" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> VBA can calculate just a single range. Something like:
>
> Sub CalcRange
> Sheets("Data").Range("E:F").Calculate
> End Sub
>
> You can have calculation option to Manual and use a button to call this
> procedure.
>
> HTH
> Kostis Vezerides
>

Is there a way I could get excel to run this code automatically, say every
minute?
"vezerid" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> VBA can calculate just a single range. Something like:
>
> Sub CalcRange
> Sheets("Data").Range("E:F").Calculate
> End Sub
>
> You can have calculation option to Manual and use a button to call this
> procedure.
>
> HTH
> Kostis Vezerides
>

Yes, you can use the Application.OnTime method. I am providing the full
set of subs that you need. You will need a global variable to remember
the next time a calculation is scheduled, so that you can stop periodic
calculation:

Public NextSchedule As Date

Sub CalcRange()
Sheets("Data").Range("E:F").Calculate
End Sub

Sub StartPeriodicCalculation()
Call CalcRange
NextSchedule = Now + TimeValue("00:01:00")
Application.OnTime NextSchedule, "StartPeriodicCalculation"
End Sub

Sub StopPeriodicCalculation()
Application.OnTime NextSchedule, "StartPeriodicCalculation", , False
End Sub

You call Start once. It will take care of subsequent schedules. Then
you call Stop when you are done. In fact, it might be a better idea if
you place the body of Stop in Workbook_BeforeClose, so that it is
guaranteed to work even if you forget to call Stop explicitly.

Beware, I have used OnTime in the past, even though I tried ways of
ensuring that the scheduling stops, sometimes the cancellation did not
work. The result was that Excel was restarting to execute the scheduled
subs.

HTH
Kostis Vezerides

Thank you very much, that is fantastic.
"vezerid" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Yes, you can use the Application.OnTime method. I am providing the full
> set of subs that you need. You will need a global variable to remember
> the next time a calculation is scheduled, so that you can stop periodic
> calculation:
>
> Public NextSchedule As Date
>
> Sub CalcRange()
> Sheets("Data").Range("E:F").Calculate
> End Sub
>
> Sub StartPeriodicCalculation()
> Call CalcRange
> NextSchedule = Now + TimeValue("00:01:00")
> Application.OnTime NextSchedule, "StartPeriodicCalculation"
> End Sub
>
> Sub StopPeriodicCalculation()
> Application.OnTime NextSchedule, "StartPeriodicCalculation", , False
> End Sub
>
> You call Start once. It will take care of subsequent schedules. Then
> you call Stop when you are done. In fact, it might be a better idea if
> you place the body of Stop in Workbook_BeforeClose, so that it is
> guaranteed to work even if you forget to call Stop explicitly.
>
> Beware, I have used OnTime in the past, even though I tried ways of
> ensuring that the scheduling stops, sometimes the cancellation did not
> work. The result was that Excel was restarting to execute the scheduled
> subs.
>
> HTH
> Kostis Vezerides
>

Thanks for the feedback. Glad to be of help.

Kostis