The PivotTable report will not fit on the sheet. Do you want to show as much as possible?

[carloExcel]

When I create a pivot table from a dataset of 600000 Rows It appears this error message. What does it exactly means?

What are the causes of this message?

The pivot table I want to create has 5 row fields, 2 filter fields and 2 calculated fields.

Set PCache = master.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange.Address(1, 1, x1A1, External:= True))

Set PTable= PCache.CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), TableName:=pivotTableName)

On google I could not find any answer.

I will be happy for any answer

 

[Alex Blakenburg]

I am guessing that your 5 rows are fairly unique and hence the 600,000 records are not being reduced enough so that when subtotal are included into the pivot table your pivot is exceeding the 1M + row limit.
If you say OK to that message box and then remove some of the subtotalling you may be able to bring the number of rows down to under the 1M row limit.

I would however suggest that if your "summarised" data has that many records, it is not fit for purpose.

 

[carloExcel]

Thank you Alex for your answer! Now I understood

I did not know any sheet can contain maximum 1 million rows (precisely 1048576).

With the first field I reach already 312443 rows, with the 3rd 937323 and with the 4th I go out of the limit. And that is the moment I get the error message

If I put

PSheet.PivotTables(pivotName).PivotFields(rowField1).Subtotals(1) = False

for all the row fields and I put these lines (Subtotals(1) = False) before inserting the fields

PSheet.PivotTables(pivotName).PivotFields(rowField1) .Orientation = xlRowField
PSheet.PivotTables(pivotName).PivotFields(rowField1) .Position = 1

I can keep the number of lines at 312443 and make it without getting the error message.

Thanks a lot for your answer!