Hi All,

I apologise in advance for the lengthy mail.
I have a major file that:
1. Imports data from SQL Server (between 500 and 30 000 rows depending
on certain criteria) and places this on my first sheet in columns A to
AD
2. Imports additional data (same amount of rows as above) and places
this into six additional sheets.These sheets are used to do
calculations (in one sheet up to column FE!)
3. Places formulas in the first sheet from columns AE to CQ for each
row of data

After retrieving the data the formulas in the six calculation sheets
are populated using something similar to the sub below:
Application.StatusBar = "Preparing to do calculations"
'Area Calcs
Worksheets("AreaCalcs").Activate
Worksheets("AreaCalcs").Range("A3").Select
Range(ActiveCell.End(xlDown), ActiveCell.End(xlToRight)).Clear
Range("A2").Select
Range(ActiveCell, ActiveCell.End(xlToRight)).Copy
Range("A2:A" & intRowCount + 1).Select
ActiveSheet.Paste
With Application
.CutCopyMode = False
.Calculate
.StatusBar = "Area calculations complete"
End With
ActiveWorkbook.Names.Add Name:="tblAreaCalcs", _
RefersToR1C1:="='AreaCalcs'!R1C1:R" & intRowCount + 1 &
"C121"
Range("A3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

As can be seen, the formulas "wait" in the second row and are copied
down...

After this is complete I then place formulas in columns AE to CQ on the
first sheet using the method illustrated he

' Range("AE2").Select
' ActiveCell.FormulaR1C1 = _
'
"=IF(AND(RC[-20]=""PANL"",RC[-21]=""Stope""),AreaCalcs!RC[-29],0)"
' Selection.Copy
' Range(ActiveCell, ActiveCell(intCount, 1)).Select
' ActiveSheet.Paste
' Application.Calculate
' Selection.Copy
' Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
' :=False, Transpose:=False

which I subsequently replaced with the code here under after reading
that selecting and activating slows processing down

Set rng = Range("AE2:AE" & intRowCount + 1)
rng =
"=IF(AND(RC[-20]=""PANL"",RC[-21]=""Stope""),AreaCalcs!RC[-29],0)"
Application.Calculate
rng.Copy
rng.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

(The liberal use of Paste Special Values is to decrease the save size
of the file which sometimes reaches 300MB!)
This whole process takes up to a half hour for the larger data sets,
and whereas the six sheets seeem to go quickly the populating of the
columns in the first sheet seems soooo slow.Without using a stopwatch,
I am convinced that the second code is slower, or if better, only
marginally....My short questions for such a long story are...am I on
the right track? Is there quicker/more efficient ways?

Hi Mike,

in my experience, copying formulas is quicker than populating each cell
with formula.

You can save some time if you set application.calculation to
manual/automatic and application.screenupdating to false/true. You can
also decrease number of dots in your code by using with .. end with
statements. If you use loops, be sure that any unnecessary code is not
inside the loop.

Regards,
Ivan

Hi Mike,

For performance tips, visit Charles William's site:

www.decisionmodels.com

--
Kind regards,

Niek Otten

"Mike" wrote in message oups.com...
| Hi All,
|
| I apologise in advance for the lengthy mail.
| I have a major file that:
| 1. Imports data from SQL Server (between 500 and 30 000 rows depending
| on certain criteria) and places this on my first sheet in columns A to
| AD
| 2. Imports additional data (same amount of rows as above) and places
| this into six additional sheets.These sheets are used to do
| calculations (in one sheet up to column FE!)
| 3. Places formulas in the first sheet from columns AE to CQ for each
| row of data
|
| After retrieving the data the formulas in the six calculation sheets
| are populated using something similar to the sub below:
| Application.StatusBar = "Preparing to do calculations"
| 'Area Calcs
| Worksheets("AreaCalcs").Activate
| Worksheets("AreaCalcs").Range("A3").Select
| Range(ActiveCell.End(xlDown), ActiveCell.End(xlToRight)).Clear
| Range("A2").Select
| Range(ActiveCell, ActiveCell.End(xlToRight)).Copy
| Range("A2:A" & intRowCount + 1).Select
| ActiveSheet.Paste
| With Application
| .CutCopyMode = False
| .Calculate
| .StatusBar = "Area calculations complete"
| End With
| ActiveWorkbook.Names.Add Name:="tblAreaCalcs", _
| RefersToR1C1:="='AreaCalcs'!R1C1:R" & intRowCount + 1 &
| "C121"
| Range("A3").Select
| Range(Selection, Selection.End(xlToRight)).Select
| Range(Selection, Selection.End(xlDown)).Select
| Selection.Copy
| Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
| SkipBlanks _
| :=False, Transpose:=False
|
| As can be seen, the formulas "wait" in the second row and are copied
| down...
|
| After this is complete I then place formulas in columns AE to CQ on the
| first sheet using the method illustrated he
|
| ' Range("AE2").Select
| ' ActiveCell.FormulaR1C1 = _
| '
| "=IF(AND(RC[-20]=""PANL"",RC[-21]=""Stope""),AreaCalcs!RC[-29],0)"
| ' Selection.Copy
| ' Range(ActiveCell, ActiveCell(intCount, 1)).Select
| ' ActiveSheet.Paste
| ' Application.Calculate
| ' Selection.Copy
| ' Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
| SkipBlanks _
| ' :=False, Transpose:=False
|
| which I subsequently replaced with the code here under after reading
| that selecting and activating slows processing down
|
| Set rng = Range("AE2:AE" & intRowCount + 1)
| rng =
| "=IF(AND(RC[-20]=""PANL"",RC[-21]=""Stope""),AreaCalcs!RC[-29],0)"
| Application.Calculate
| rng.Copy
| rng.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
| SkipBlanks _
| :=False, Transpose:=False
|
| (The liberal use of Paste Special Values is to decrease the save size
| of the file which sometimes reaches 300MB!)
| This whole process takes up to a half hour for the larger data sets,
| and whereas the six sheets seeem to go quickly the populating of the
| columns in the first sheet seems soooo slow.Without using a stopwatch,
| I am convinced that the second code is slower, or if better, only
| marginally....My short questions for such a long story are...am I on
| the right track? Is there quicker/more efficient ways?
|

Niek, Thanks for the advice. I went to the site and purchased the xla
anyway. Probably take me a month of Sundays to go thru' it all and see
how it can improve my workbook's perrformance. Have you got any advice
that can assist in the short term?

Ivan, How does "decreasing number of dots" improve performance? It
sounds like good advice: but does it speed things that much?

Avoid selecting.

For example:

Range("A1").Select
Selection.Copy
Range("A2").Select
Activesheet.Paste

Can be replaced by:

[a1].copy [a2]

This is much faster

--
Kind regards,

Niek Otten

"Mike" wrote in message ups.com...
| Niek, Thanks for the advice. I went to the site and purchased the xla
| anyway. Probably take me a month of Sundays to go thru' it all and see
| how it can improve my workbook's perrformance. Have you got any advice
| that can assist in the short term?
|