Calculating(Processors(2)): %
 

I am running the code below in a .xlsm '07 spreadsheet. The problem I'm
having is the code runs painfully slow and I can't figure out why. As soon
as it starts my CPU usage spikes up to 52%, 51% of which is EXCEL.EXE. I
also get "Calculating(Processors(2)): %" in my status bar constantly
calculating "something" and showing me percentages. I tested this code using
1000 rows of data and it took ~8 mins, 2000 rows took ~15 mins, and 4000 rows
took ~28 mins. Each row of data has information in columns A-Q. What is
causing this latency? All the code does is sort and move data from one
worksheet to another worksheet in the same workbook.

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 doesn't 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)
Loop Until RowCount = LastRow
End With
With Sheets("Tally Sheet")
'This code inputs the formulas to map over the info from the $7 Report
'for each rep being reviewed. &Y&2 refers to cell Y2 on the tally sheet
'that contains the following formula:
':=IF(ISNA(TEXT(LOOKUP(A1,X!D1:D12,X!E1:E12),)&" 09 $7
Report.xls"),"",TEXT(LOOKUP(A1,X!D1:D12,X!E1:E12), )&" 09 $7 Report.xls")
'This basically says that if any info from the $7 report causes a NA
error
'then do nothing else use the formula to locate the pertinent info in
the $7 Report,
'and mirror the info in the tally sheet.
'The $7 Report must be saved in the following format to work: Feb 09
$7 Report
TSPasteRow = TSPasteRow - 8
TSStartRow = 6
For RowCount = TSStartRow To TSPasteRow Step 8
If TSStartRow <= TSPasteRow Then
.Range("Z" & TSStartRow).Formula = _
"=IF(ISNA(VLOOKUP($A" & TSStartRow _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$20000""),11,FALSE)),""""," _
& "VLOOKUP($A" & TSStartRow _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$20000""),11,FALSE))"
.Range("AA" & TSStartRow).Formula = _
"=IF(ISNA(VLOOKUP($A" & TSStartRow _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$20000""),6,FALSE)),""""," _
& "VLOOKUP($A" & TSStartRow _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$20000""),6,FALSE))"
.Range("AB" & TSStartRow).Formula = _
"=IF(ISNA(INDIRECT(""'[""&$Y$2&""]By_Function'!$B$6"")),""""," & _
"INDIRECT(""'[""&$Y$2&""]By_Function'!$B$6""))"
.Range("AC" & TSStartRow).Formula = _
"=IF(ISNA(VLOOKUP($A" & TSStartRow _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$20000""),7,FALSE)),""""," _
& "VLOOKUP($A" & TSStartRow _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$20000""),7,FALSE))"
.Range("Z" & TSStartRow & ":AC" & (TSStartRow + 2)).FillDown
TSStartRow = TSStartRow + 8
End If
'The next 2 lines of code show a Progess Indicator
PctDone = (TSStartRow - 8) / TSPasteRow
Call UpdateSevenRProgress(PctDone)
Next RowCount
Unload SevenRProgressIndicatorF
End With
End Sub

Calculating(Processors(2)): %
 

While waiting for an expert response, I'll entertain you with my random
babbling :)

1. It appears that in the latter part of your macro you are adding formulas,
but I don't see you setting the application.calculation to false at the
beginning of your procedure (and reseting it to automatic, or whatever your
preference is at the end). Using application.calculation = false will keep
those formulas from continuously recalculating as you change the sheet, and
that should save you some processing.

2. I recall numerous complaints about the speed of worksheet changes like
row insertion. Consider an alternate approach of loading the data into
memory, and manipulating everything in memory, then write it back to the
sheet just once (eliminate all the within-loop changes). I'd bet money that
this would have a tremendous impact on your processing speed.

3. As an alternative to #2, I suppose you could just copy the data to a new
sheet, and skip down "x" rows instead of inserting rows. That should still be
faster than inserting rows, although it does leave you with an extra sheet at
the end of your processing...

4. That is one monster formula. I didn't try to dissect it, but looking at
all of those indirects, I have to wonder if there isn't a way to shorten the
formula, and thereby also decrease processing time. If you were willing to
loop instead of using autofill, you could pass the range references from your
variables instead of using indirects. Some of the other pieces might also be
shrinkable (I'm thinking the vlookups) but like I said, I didn't dissect it.
Consider this option- do these values need to change because the source data
ranges will be changing, or is this all just to pull a summary number? If the
source data isn't expected to change, can you just calculate the value that
the formula would return (in VBA), and just paste the final value into the
destination cell(s)? I guess to see if this really matters, but a time stamp
just before and just after that formula line, and see how long it takes to
process just that one step. If it is relatively short, then it isn't worth
worrying about.
add: debug.print format(now(),"HH:MM:SS") on either end of that formula
line, then look in the debug window to see how much time elapsed between the
two result lines.

Best,
Keith

"Bishop" wrote:

I am running the code below in a .xlsm '07 spreadsheet. The problem I'm
having is the code runs painfully slow and I can't figure out why. As soon
as it starts my CPU usage spikes up to 52%, 51% of which is EXCEL.EXE. I
also get "Calculating(Processors(2)): %" in my status bar constantly
calculating "something" and showing me percentages. I tested this code using
1000 rows of data and it took ~8 mins, 2000 rows took ~15 mins, and 4000 rows
took ~28 mins. Each row of data has information in columns A-Q. What is
causing this latency? All the code does is sort and move data from one
worksheet to another worksheet in the same workbook.

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 doesn't 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)
Loop Until RowCount = LastRow
End With
With Sheets("Tally Sheet")
'This code inputs the formulas to map over the info from the $7 Report
'for each rep being reviewed. &Y&2 refers to cell Y2 on the tally sheet
'that contains the following formula:
':=IF(ISNA(TEXT(LOOKUP(A1,X!D1:D12,X!E1:E12),)&" 09 $7
Report.xls"),"",TEXT(LOOKUP(A1,X!D1:D12,X!E1:E12), )&" 09 $7 Report.xls")
'This basically says that if any info from the $7 report causes a NA
error
'then do nothing else use the formula to locate the pertinent info in
the $7 Report,
'and mirror the info in the tally sheet.
'The $7 Report must be saved in the following format to work: Feb 09
$7 Report
TSPasteRow = TSPasteRow - 8
TSStartRow = 6
For RowCount = TSStartRow To TSPasteRow Step 8
If TSStartRow <= TSPasteRow Then
.Range("Z" & TSStartRow).Formula = _
"=IF(ISNA(VLOOKUP($A" & TSStartRow _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$20000""),11,FALSE)),""""," _
& "VLOOKUP($A" & TSStartRow _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$20000""),11,FALSE))"
.Range("AA" & TSStartRow).Formula = _
"=IF(ISNA(VLOOKUP($A" & TSStartRow _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$20000""),6,FALSE)),""""," _
& "VLOOKUP($A" & TSStartRow _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$20000""),6,FALSE))"
.Range("AB" & TSStartRow).Formula = _
"=IF(ISNA(INDIRECT(""'[""&$Y$2&""]By_Function'!$B$6"")),""""," & _
"INDIRECT(""'[""&$Y$2&""]By_Function'!$B$6""))"
.Range("AC" & TSStartRow).Formula = _
"=IF(ISNA(VLOOKUP($A" & TSStartRow _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$20000""),7,FALSE)),""""," _
& "VLOOKUP($A" & TSStartRow _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$20000""),7,FALSE))"
.Range("Z" & TSStartRow & ":AC" & (TSStartRow + 2)).FillDown
TSStartRow = TSStartRow + 8
End If
'The next 2 lines of code show a Progess Indicator
PctDone = (TSStartRow - 8) / TSPasteRow
Call UpdateSevenRProgress(PctDone)
Next RowCount
Unload SevenRProgressIndicatorF
End With
End Sub