+ Reply to Thread
Results 1 to 2 of 2

Problem with Remove Subtotal in Macro

  1. #1
    Jerry Cropanese
    Guest

    Problem with Remove Subtotal in Macro

    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




  2. #2
    Jim Cone
    Guest

    Re: Problem with Remove Subtotal in Macro

    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


    "Jerry Cropanese" <[email protected]> wrote in
    message news:[email protected]...
    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




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1