Copy down takes a long way. What is quicker

[Chrissirhc]

Hi,

In a VBA macro I need to copy down some formulae.

Range("dataRange").Copy
Range("details").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

The copy down line is x columns wide and I'm copying down 11000 rows. It takes long and sometimes runs out of memory.

Is there a better way to do this?

 

[Chrissirhc]

Sorry x is 64. The data is 64 columns wide.

And thanks in advance,

Chris

 

[xlbo]

how are you copying the formula when you are using PasteSpecial>Values????

In terms of your question, 64 cols by 11000 rows = 704000 cells to copy into - are you really surprised that it takes a while ??

 

[Chrissirhc]

Oopps wrong bit of code I had posted:

Range("line").Select
Selection.AutoFill Destination:=Range(copyDownToString), Type:=xlFillDefault

Anyway to speed it up? Thanks, chris

 

[xlbo]

only other way is to fill the entire range spcifically - can't see that being any faster.

My point remains - you have to fill 704000 cells with a formula or data. It will take a while. End of story.

you could try beefing up your RAM as this is likely to have more of an effect than anything else but at the end of the day, it's a large dataset - you can't get away from that...

 

[Zathras]

You could try turning calculation to manual.

You could also bracket the code with
[tt]
Application.ScreenUpdating = False
(your code goes here...)
Application.ScreenUpdating = True
[/tt]
I believe there are FAQ's that cover some of these issues.

 

[Chrissirhc]

Thanks. I had already turned screenupdating off.

How would setting the calculation to manual help?

Thanks,

Chris

 

[PHV]

Application.Calculation = xlCalculationManual
your code here
Application.Calculation = xlCalculationAutomatic

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Gavona]

Given your memory problem consider: Do you need formulae in all these cells or just the results?
Maybe:
set calculation to manual,
copy 1 col of formulae,
calculate the workbook, sheet or even just the range*
Copy and pastespecial to values
repeat for next column

My solution:
Have the formulae 2 rows above the column headings.
Create named ranges for each column using the coilumn headings (Select the all the column headings of interest and down to the bottom row, Insert, Name, Create, TorRow)
Run this routine:

Sub FormulaCopy()
'MyName is the name of the range to which data is to be copied _
 it does not include the heading.
'The formulae are 2 rows above the heading i.e.
'   Formula
'   [blank]
'   Heading
'   first cell of range named ....
' Macro created 17/05/2005 by gk
'
Dim Myprompt As String, Response As String, Style As Integer

Myprompt = "For each cell in current selection the macro copies formulae in the second row above the selected cell to all cells below the selected cell. (actually there must be a named range equal to the text in the selected cell and it is this range that is copied to.  Calculation may be set to manual as the routine calculates the pasted cells (only) and then pastes them to values"
Style = vbOKCancel + vbCritical + vbDefaultButton2    ' Define buttons.
Response = MsgBox(Myprompt, Style, "Copy Formulae Macro")
If Response = vbCancel Then Exit Sub

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

For Each c In Selection
Application.StatusBar = "Applying Formulae to " + c.Value
With Range(Replace(c.Value, " ", "_"))
    .Cells(-2, 1).Copy
    .Cells.PasteSpecial (xlPasteAll)
    .Calculate
    .Cells.Copy
    .Cells.PasteSpecial (xlPasteValues)
End With
Next c

Myprompt = "Macro Finished - calculation set to automatic"
Style = vbOK + vbCritical + vbDefaultButton2   ' Define buttons.
Response = MsgBox(Myprompt, Style, "Copy Formulae Macro")
If Response = vbCancel Then Exit Sub
Application.StatusBar = "Setting calculation to automatic"
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.StatusBar = ""
End Sub

Gavin

 

[Gavona]

OOPs, that was an early version of the code I use. It does not handle the status bar properly. The last line should be:

Application.statusbar = False

Gavin

 

[tektipdjango]

if you want to copy the formula of the first line in all the rows, you'd better put your formula in a name (ex 'calc') and change in the 1 line the formula's like this
=calc

to get the right formula select the first cell of the first line then record the code to change the formula via record macro in copying the formula then paste it in 'defining names' under the 'calc' name

then copy in all the rows this formula

=calc

If the formula change use the vba statement like this

i try it on the range E13:BC10013
the formula in E13 was =Feuil1!CH2 & Feuil1!CK2


ActiveWorkbook.Names.Add Name:="calc", RefersToR1C1:="=Feuil1!R[-11]C[82] & Feuil1!R[-11]C[84]"

All the rows will be updated using this new formula

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​