pincivma
01-12-2008, 10:24 AM
Doing subtotals in Excel 2000 Hi there
I have a line of code in a macro that does subtotals. First I sort column A and then I do the subtals. Next I go to level 2 of the subtotals. Below is the code that I'm sure all of you will recognize.
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(4), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
When I run the macro, the above code starting with "Selection.Subtotal" and ending with the word "true" is highlighted in yellow. When I hit the debug button, I get this error:
"Run-time error ?1004?
To prevent possible loss of data, Microsoft Excel cannot shift nonblankcells off the worksheet.
Try to locate the last nonblank cell by pressing CTRL+END, and delete or clear all in cells between the last cell and the end of your data. Then select cell A1 and save your worksheet to reset the last cell used."
I know why I got the error. My subtotals go beyond row 65536.
Is there a better code that does subtotals directly at level 2 so that my data does not go beyond row 65536??
Thanks,
Mario
I have a line of code in a macro that does subtotals. First I sort column A and then I do the subtals. Next I go to level 2 of the subtotals. Below is the code that I'm sure all of you will recognize.
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(4), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
When I run the macro, the above code starting with "Selection.Subtotal" and ending with the word "true" is highlighted in yellow. When I hit the debug button, I get this error:
"Run-time error ?1004?
To prevent possible loss of data, Microsoft Excel cannot shift nonblankcells off the worksheet.
Try to locate the last nonblank cell by pressing CTRL+END, and delete or clear all in cells between the last cell and the end of your data. Then select cell A1 and save your worksheet to reset the last cell used."
I know why I got the error. My subtotals go beyond row 65536.
Is there a better code that does subtotals directly at level 2 so that my data does not go beyond row 65536??
Thanks,
Mario