You've got the right property to turn off auto-calc and turn it back on:
Application.Calculation = xlCalculationManual
.... all your code that doesn't need calculations to be kept current ...
Application.Calculation = xlCalculationAutomatic
(BTW, when I turn auto-calc off and on, I often turn screenupdating off and
back on. That property is application.ScreenUpdating and the values are
False then True. Particularly if you're looping, it can save visual
dizziness and LOTS of time.)
As for selecting a varying range, you might do something like
Dim WhichCol as integer
and then
WhichCol = Selection.Column
Range(Cells(2, WhichCol), Cells(35, WhichCol)).Select

--Bruce

"Derek Y via OfficeKB.com" wrote:

Works perfectly!!!!!!!!! Thank you veryyy much.

I would like to ask for just one more tid-bit of advice/help.

I know how to write macros by recording them, and editing them like after
they are recording I can see the cell numbers and whatnot and I can play
around with that stuff, no problem. But here is what I would like to do:

Have the cursor on say W2. And W2 already has that formula in it.

{=if(valid,vlookup(A2,FINAL_ALL,ColNum,0),"")}

But all the rows below it are completly blank.

I would like to be able to push CTRL SHIFT something and have excel

1) turn off automatic calculation (because it would kill it)
2) drag that formula down 60,000 rows in that column
3) calculate what should be in there (as if i would manually push F9 now)
4) copy that entire column
5) paste just VALUES for that column

but I want to be able to run this macro on any column I may be in (so X2, Y2,
etc.)

for parts 4 and 5, the following works....

Sub FreezeColumn()
With ActiveCell.Columns
.EntireColumn.Copy
.EntireColumn.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With

Application.CutCopyMode = False

End Sub

and as far as parts 2 and 3

Sub Macro1()
'
Selection.AutoFill Destination:=Range("X2:X35"), Type:=xlFillDefault
Range("X2:X35").Select
Calculate
End Sub

i see that calculate kind of takes the place of my F9, but I want to make
sure that before the macro is run that the auto calculation is turned off and
turned back on after. Also I dont know how to make this work in general for
any column that is selected when that particular cell is selected.

This may help for the auto calculation and manual calculation business:

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

Any additional help you could provide is appreciated.

Derek

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200511/1