User input to calculate specific column
 

I have two worksheets that both have 65k rows and when I do a formula it
takes forever because it recalculates all columns with formula's -- this take
about ten minutes on my laptop. Is there a snippet of code that I can put in
a macro that will ask me sheet name and column to calculate so it will only
do that specific column?

Jason

User input to calculate specific column
 

From VBA help:.

Calculate Method
See AlsoApplies ToExampleSpecificsCalculates all open workbooks, a
specific worksheet in a workbook, or a specified range of cells on a
worksheet, as shown in the following table.

To calculate Follow this example
All open workbooks Application.Calculate (or just Calculate)
A specific worksheet Worksheets(1).Calculate
A specified range Worksheets(1).Rows(2).Calculate

expression.Calculate
expression Optional for Application, required for Worksheet and
Range. An expression that returns an object in the Applies To list.

Example
This example calculates the formulas in columns A, B, and C in the used
range on Sheet1.

Worksheets("Sheet1").UsedRange.Columns("A:C").Calc ulate


Rookie_User wrote:
I have two worksheets that both have 65k rows and when I do a formula it
takes forever because it recalculates all columns with formula's -- this take
about ten minutes on my laptop. Is there a snippet of code that I can put in
a macro that will ask me sheet name and column to calculate so it will only
do that specific column?

Jason

User input to calculate specific column
 

I'm not sure how the whole "Calculate" process works but give this a
shot:
Sub CalcTest()
Dim CalcMode As String
Dim r As Range
CalcMode = Application.Calculation
Application.Calculation = xlCalculationManual
Set r = Application.InputBox("Please Select Column to Calculate",
"Calculating...", Type:=8)
If Not r Is Nothing Then
Set r = r.EntireColumn
r.Calculate
End If
Application.Calculation = CalcMode
End Sub


HTH

Charles Chickering
Rookie_User wrote:
I have two worksheets that both have 65k rows and when I do a formula it
takes forever because it recalculates all columns with formula's -- this take
about ten minutes on my laptop. Is there a snippet of code that I can put in
a macro that will ask me sheet name and column to calculate so it will only
do that specific column?

Jason

User input to calculate specific column
 

Jason,

To follow up on the calclulate method, set your workbook to manual
recalc and place this in the worksheet module in question. Change the
range to your column(s)

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A:A")) Is Nothing Then
ActiveCell.Calculate
End If
End Sub



Rookie_User wrote:
I have two worksheets that both have 65k rows and when I do a formula it
takes forever because it recalculates all columns with formula's -- this take
about ten minutes on my laptop. Is there a snippet of code that I can put in
a macro that will ask me sheet name and column to calculate so it will only
do that specific column?

Jason

User input to calculate specific column
 

I will try the above, this is what I have so far. I do have the recalc all
set to manual.

Sub Col_Calc()
Dim coltocal As String
coltocal = Trim(InputBox(Prompt:="Enter the column to calculate--"))
Worksheets("Part1").UsedRange.Columns(coltocal).Ca lculate
End Sub

" wrote:

Jason,

To follow up on the calclulate method, set your workbook to manual
recalc and place this in the worksheet module in question. Change the
range to your column(s)

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A:A")) Is Nothing Then
ActiveCell.Calculate
End If
End Sub



Rookie_User wrote:
I have two worksheets that both have 65k rows and when I do a formula it
takes forever because it recalculates all columns with formula's -- this take
about ten minutes on my laptop. Is there a snippet of code that I can put in
a macro that will ask me sheet name and column to calculate so it will only
do that specific column?

Jason