Doing subtotals in Excel

[pincivma]

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

[Bob Phillips]

[vba]

Dim LastRow As Long
Dim rng As Range

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

Set rng = Range("A1").Resize(LastRow, 5)
rng.Sort Key1:=Range("A2"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
rng.Subtotal GroupBy:=1, _
Function:=xlSum, _
TotalList:=Array(4), _
Replace:=True, _
PageBreaks:=False, _
SummaryBelowData:=True
.Outline.ShowLevels RowLevels:=2
End With
[/vba]

[pincivma]

Hi xld

I ran your code step by step and I still got the error on the code below since the code

rng.Subtotal GroupBy:=1, _
Function:=xlSum, _
TotalList:=Array(4), _
Replace:=True, _
PageBreaks:=False, _
SummaryBelowData:=True

shows all the data with the subtotals and the data once again goes beyond the last row 65536. I guess there is no way around it.

Mario

[Bob Phillips]

Can you post your workbook, I would like to see this?

[pincivma]

Hi again xld

The file is quite big. Even the zipped file exceeds the allowable upload. Hence I cannot send it to you. I can send it to your e-mail address (if you give it to me) if you can receive 4 or 5 MB of data. But take my word for it, the subtotals still exceed the last row of the Excel spreadsheet at level 3 before going to level 2.

Mario

[Bob Phillips]

Are you saying that the data goes to some line near the end (60,000+)?

[pincivma]

Yes. The raw data, before the subtotal is executes, goes to line 62780. Therefore, when you excecute the subtotals the data goes beyond the last row of the Excel spreadsheet and hence the error.

Mario

[Bob Phillips]

So the answer must be that you are trying to add more than 2,756 subtotals, and as you only have 2,576 rows left, it bombs.

You could use VBA to create subtotals in anoter column rather than a new row, but I am not sure how quick it will be.

[pincivma]

I am using an entire new spreadsheet to do the subtotals so the suggestion of using another column does not work. I believe I have to split the data into 2 parts, part A and B, do subtotals for part A and part B using 2 separate spreadsheets, then go to level 2 on both and then combine the A and B into one spread sheet. All of this can be done using VBA. I see no other way out.

Mario

[oxs@ca.rr.co]

I believe the limit for rows has increased by an order of magnitude.

good luck

Otto1939

[pincivma]

Thanks. I'm working on splitting the data now. Wish me luck.

Mario