Macro - set calc off

[Grace]

Some of my macros seem to be slowed down by having to calculate after each
operation and this is not necessary. So, initially, I want to disable calc
and do it only once at the end. What is the best macro command for this?

Thanks,
Grace

 

[Chip Pearson]

Grace,

At the beginning of the procedure, use

Application.Calculation = xlCalculationManual

At the end of the procedure, use

Application.Calculation = xlCalculationAutomatic



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Bob Flanagan]

Grace, one way to get the required statements is just to record a macro of
the action.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Grace]

Hey Bob,

I do know this. In fact, this is how I create most of my code! In this
case, I got

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False

is this the same thing as the one-liner Chip just advised?

The one thing that is really confusing me is trying to record relative
references. I know how to set it, now, but it still doesn't seem to work as
I would hope, even with that option selected. For example, let's say I
select a block of cells but want to expand it by six columns to the right.
When I record, it shows me something like f16 going to L16, but this does
not work the next time, when my block is not ending in cell f16. Can I get
it to record this kind of thing? By the way, someone kindly tell me what
the VB command is for expanding a selected range by six columns to the
right, in case the recording doesn't work.

Thanks,
Grace

 

[Tom Ogilvy]

set rng = selection.Resize(,6)

 

[Grace]

Thanks!

Grace

set rng = selection.Resize(,6)

--
Regards,
Tom Ogilvy

work macro