Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup column based on user input | Excel Worksheet Functions | |||
formula to calculate a specific percent of a column of different p | Excel Worksheet Functions | |||
Trying to select a specific range based on the time value of user form input | New Users to Excel | |||
Validate user input with specific date format | Excel Programming | |||
How can I calculate user input from a combo box into a formula? | Excel Worksheet Functions |