Problem with Remove Subtotal in Macro

[Jerry Cropanese]

I built a macro using excel 97 that does a subtotal of a list of 100 - 5000
rows x 6 columns and later removes the subtotal. This macro took
approximately 15 to 30 seconds to run using excel 97 under a win 98
platform. We finally upgraded to win xp and excel 2002. As soon as the
macro runs the line "Selection.RemoveSubtotal", the whole thing reduces to a
crawl and I end up having to blow off excel or it will sit for over 1/2
hour.

I tried to run the macro to the point where the problem line exists and then
do the remove subtotal manually but I receive the same result.

Can you see any reason for this problem?

Thanks,

Jerry

Here's part of the macro:

Selection.SUBTOTAL GroupBy:=3, Function:=xlCount, TotalList:=Array(3,5),
_
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Columns("G:H").Select
Selection.ClearContents
Range("G2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]=RC[-4],""X"","""")"
Range("H2").Select
ActiveCell.FormulaR1C1 = "=R[1]C[-1]"
Range("B2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 5).Range("A1").Select
ActiveCell.FormulaR1C1 = "x"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "x"
Range("G2:H2").Select
Selection.Copy
Range(ActiveCell, ActiveCell.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Application.CutCopyMode = False
Range("A2").Select
Selection.RemoveSubtotal

 

[Jim Cone]

Jerry,

Is "A2" inside the subtotaled list?
Probably so, but I had to ask.

I would suspect a corrupted workbook and or module.
The best answer might be to replace them.

1. If at all practical, copy your data into a new workbook.
2. Copy the code from the module into Notepad.
3. Insert a new module (into the old/new workbook),
4. Copy the code from Notepad and paste into the new module.
5. Delete the old module, if still using the original workbook.

Regards,
Jim Cone
San Francisco, USA


message I built a macro using excel 97 that does a subtotal of a list of 100 - 5000
rows x 6 columns and later removes the subtotal. This macro took
approximately 15 to 30 seconds to run using excel 97 under a win 98
platform. We finally upgraded to win xp and excel 2002. As soon as the
macro runs the line "Selection.RemoveSubtotal", the whole thing reduces to a
crawl and I end up having to blow off excel or it will sit for over 1/2
hour.

I tried to run the macro to the point where the problem line exists and then
do the remove subtotal manually but I receive the same result.
Can you see any reason for this problem?
Thanks,
Jerry

Here's part of the macro:

Selection.SUBTOTAL GroupBy:=3, Function:=xlCount, TotalList:=Array(3,5),
_
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Columns("G:H").Select
Selection.ClearContents
Range("G2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]=RC[-4],""X"","""")"
Range("H2").Select
ActiveCell.FormulaR1C1 = "=R[1]C[-1]"
Range("B2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 5).Range("A1").Select
ActiveCell.FormulaR1C1 = "x"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "x"
Range("G2:H2").Select
Selection.Copy
Range(ActiveCell, ActiveCell.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A2").Select
Selection.RemoveSubtotal