Is there any way to stop a range from calculating without setting EnableCalculation Off?

I have a large range with a lot of dependent cells that I don't want to calculate until I tell it to. It uses SUMIF().
But I don't want to turn off Calulation or EnableCalulcation because they take too long to turn back on.
Is there some way to prevent the range from recalculating?

I thought of using the formula =If(Range("myRecalc"),SUMIF(.....),???) and replace myRacalc with "False" until I'm ready.
But I can't figure out what to put for the Value_If_False that wouldn't recalculate when I put the "False" into myRecalc.

I wonder whether you can use a macro using first xlcalcualtion=manual in the beginning and at the end change it to automatic

try something like this

if you record the macro doing the formula you will know how to write a macro for this formula.

It takes too long to set Calculation back to Automatic, that's why I'm trying to find a way around it.

too long? it only takes 5 mouse clicks, and even that, you could record a macro to do for you with just 1 click....or just F9 to recalc

also, take a look and see if you can change your data to a table, there is an option to have auto-calc on all but data tables, see if this will work for you?

What I meant by "too long" is: there are so many volatile formulas in the workbook that it takes too long to recalculate them when I set calculation back to automatic. I'm working on reducing the # of volatile formulas, but it takes time (I inherited the file and have to triple check everything I do, because I can only change a few at a time and then let them be tested for a few days to make sure there are no unforeseen consequences).

I'll look at making it a table. My hunch is that it won't work, but I won't know until I try.

Hi foxguy,

You seem to have an interesting problem: Maybe you will find this link helpful.

http://msdn.microsoft.com/en-us/library/ff700515.aspx

HTH

I have read that before. It's helping me in rebuilding the workbook.
The original author did a lot of things that didn't really matter when the file was first being used, but now one of the sheets has over 12,000 rows of data in dynamic ranges (and growing every day) and every one of those rows has volatile formulas in it, and there are a lot of formulas throughout the workbook that extract data from that sheet. In addition (s)he named ranges on a lot of sheets with the same name and some of the macros build the range names within the macro (which prevents a search for the range names), so I have to be very careful about changing the range names or converting the ranges to fixed addresses (to get rid of the volatility). I have to constantly look for unforeseen consequences which takes time.