Prev: XL 2010: Parent of a Range can be a Shape?
Next: Equal list values.
From: chris on 1 Jun 2010 01:18
I too have been experiencing this problem and am keen to try the Sub SpeedUpMacro you've included, but I have probably 30 vba codes. Would I need to add this Sub SpeedUpMacro to all of those 30 or just one, and if just one, how do I know which one?

Chris



J_Knowles wrote:

Sub SpeedUpMacro()Application.ScreenUpdating = FalseApplication.
03-Feb-10

Sub SpeedUpMacro()

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

'your code goes here

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.Calculate

End Sub

HTH,
--
Data Hog


"Bishop" wrote:

Previous Posts In This Thread:

On Wednesday, February 03, 2010 11:39 AM
Bishop wrote:

Calculating(Processor(2)): %
I have a program running in a .xlsm 2007 spreadsheet that formats data and
moves the data from one worksheet to another (in the same workbook). The
problem is that it runs painfully slow. I tested it with 2000 rows @ ~8
minutes, 3000 rows @ ~15 minutes and 4000 rows @ ~28 minutes. When the
program starts my CPU usage spikes up to 52%, 51% of which is EXCEL.EXE.
Also, while it is running I have "Calculating(Processor(2)): XX%" in my status
bar. You can see the data being added one block at a time (one iteration of
the code) as it cycles through the Calculating process. I just do not
understand what is causing it to have such latency. Is there a way to make
the code more efficient so that it runs faster? What's causing the
Calculating(Processor(2)): % to pop up? I have other macros running in this
same workbook and they run fine.

My code:

'This code formats the data downloaded from Catalyst (in the Catalyst Dump
'tab) and puts in the Tally Sheet to be reviewed
Sub TallySheetRepDump()

Dim LastRow As Integer
Dim StartRow As Integer
Dim TSPasteRow As Integer 'Tally Sheet
Dim TSStartRow As Integer 'Tally Sheet
Dim RowCount As Integer
Dim EndRow As Integer
Dim CheckRow As Integer
Dim AddRow As Integer
Dim counter As Integer
Dim PCounter As Integer 'Progress Counter
Dim PctDone As Single 'Percent Done

With Sheets("Tally Sheet")
..Shapes("BigOrangeButton").Cut
End With

With Sheets("SortedRepData")
'The following line of code calculates the number of rows of data
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
'Sort by UID (column A) then by Transaction Amount (column F)
..Rows("1:" & LastRow).Sort _
Key1:=.Range("R1"), _
Order1:=xlAscending, _
Key2:=.Range("A1"), _
Order2:=xlAscending, _
Key3:=.Range("F1"), _
Order3:=xlAscending, _
Header:=xlNo

StartRow = 1
TSPasteRow = 6
RowCount = 0
'Outer loop for entire worksheet.
Do
RowCount = RowCount + 1
'Check to see if RowCount is equal to the next row. If not that
'means the name has changed and we want to capture the info for
'the current rep
If .Range("A" & RowCount) <> .Range("A" & (RowCount + 1)) Then
'If name changes make sure the rep has 3 or more transactions
EndRow = StartRow + 2
CheckRow = StartRow
AddRow = 2
'If rep has at least 3 transactions then copy the first 3 and
'move them to the Tally Sheet
If .Range("A" & StartRow) = .Range("A" & EndRow) Then
..Range("A" & StartRow & ":F" & EndRow).Copy _
Destination:=Sheets("Tally Sheet").Range("A" & TSPasteRow)
..Range("G" & StartRow & ":Q" & EndRow).Copy _
Destination:=Sheets("Tally Sheet").Range("N" & TSPasteRow)
TSPasteRow = TSPasteRow + 8
StartRow = RowCount + 1
'If rep does not have at least 3 transactions then determine how
many
'transactions they do have and add the appropriate number of rows
Else
For counter = CheckRow To EndRow
If .Range("A" & CheckRow) = .Range("A" & (CheckRow + 1))
Then
AddRow = AddRow - 1
CheckRow = CheckRow + 1
Else
..Rows(CheckRow + 1).Resize(AddRow).Insert
(xlShiftDown)
RowCount = RowCount + AddRow
..Range("A" & StartRow & ":F" & EndRow).Copy _
Destination:=Sheets("Tally Sheet").Range("A" &
TSPasteRow)
..Range("G" & StartRow & ":Q" & EndRow).Copy _
Destination:=Sheets("Tally Sheet").Range("N" &
TSPasteRow)
TSPasteRow = TSPasteRow + 8
LastRow = LastRow + AddRow
StartRow = RowCount + AddRow
Exit For
End If
Next counter
End If
End If
PctDone = (RowCount / LastRow)
Call UpdateSevenRProgress(PctDone)

On Wednesday, February 03, 2010 11:57 AM
Niek Otten wrote:

If you are moving data the workbook may be recalculated all the timeTry
If you are moving data the workbook may be recalculated all the time
Try setting calculation to Manual (and back to Automatic once your macro
finished)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

On Wednesday, February 03, 2010 1:01 PM
Phil Hibbs wrote:

Niek Otten wrote:...and I think you need to do an Application.
Niek Otten wrote:

....and I think you need to do an Application.Calculate as well when
you set it back to automatic, or the workbook will be inconsistent
until you change a cell.

Phil Hibbs.

On Wednesday, February 03, 2010 10:55 PM
J_Knowles wrote:

Sub SpeedUpMacro()Application.ScreenUpdating = FalseApplication.
Sub SpeedUpMacro()

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

'your code goes here

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.Calculate

End Sub

HTH,
--
Data Hog


"Bishop" wrote:

On Thursday, February 04, 2010 9:36 AM
Bishop wrote:

This worked like a charm! Thank you so much!
This worked like a charm! Thank you so much! What was taking 40 minutes
before now runs in less than 5 seconds!

You're a life saver.

"J_Knowles" wrote:

On Wednesday, February 24, 2010 11:27 AM
ETinKC wrote:

"Bishop" wrote:
"Bishop" wrote:

On Wednesday, February 24, 2010 11:38 AM
ETinKC wrote:

I found your post while googling "slow excel calculating 2 processor(s)" as
I found your post while googling "slow excel calculating 2 processor(s)" as I
was having the same problem as you, but was not working with Macros at the
time. I am glad you got yours fixed but thought I would post this here as
it might help other who are having the same problem but not while writing
code.

I was creating a spreadsheet template using multiple instances of indirect
to reference ranges in other sheets in the workbook that had not yet been
created. So while working on the sheet i did turn off auto calc. But even
when finished it would reference a sheet not yet created, waiting for the
user to add the tab and name it appropriately so the indirect can reference
it.

I solved the slow problem by adding an if statement before the indirect in
my formula such that the user must check a box telling the template that tab
exists before running the part of the formula with the indirect function
pointing to that tab.

Problem solved.


Submitted via EggHeadCafe - Software Developer Portal of Choice
Distributed Data Grids - Share Objects Between Windows Service and ASP.NET
http://www.eggheadcafe.com/tutorials/aspnet/953ed61f-e440-4ca5-8b7a-1bb00e33db07/distributed-data-grids-.aspx
From: GS on 1 Jun 2010 17:02
chris jamieson presented the following explanation :
> I too have been experiencing this problem and am keen to try the Sub
> SpeedUpMacro you've included, but I have probably 30 vba codes. Would I need
> to add this Sub SpeedUpMacro to all of those 30 or just one, and if just one,
> how do I know which one?
>
> Chris

Not sure where this SpeedUpMacro code came from but looks like
something I posted here some time ago in this reusable sub:

Public Sub EnableFastCode(Optional SetFast As Boolean = True)
With Application
If SetFast Then
.ScreenUpdating = False
.Calculation = xlCalculationManual
Else
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.Calculate '//update changes
End If
End With
End Sub

To use it in any procedure:

Sub SomeSub()
'dim your vars and do other stuff before running main process

'before you run main process, turn it on
EnableFastCode
'code to do lengthy stuff or lots of recalcs goes here

'at end of main process, turn it off and recalc
EnableFastCode False

'cleanup code goes here
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


 | 
Pages: 1
Prev: XL 2010: Parent of a Range can be a Shape?
Next: Equal list values.