Excel Range issue

[Rohit Nayak]

Hello Everyone,
We have a pivot table that is being populated by an Excel worksheet
range. The range is filled by data retrieved by a stored proc.
Recently we exceeded the 65k row limit of Excel. I have been successful
in breaking up the feed to the worksheet into contiguos blocks of data -
each of 65k rows.

How do I combine all these blocks into single range for giving as input
to the Pivot table?
Does the Excel range have a 65 k row limit too?




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Jim Thomlinson]

The short answer is that yes a range has a 65,535 row limit.

In the Excel Object Model a sheet object acts as a container for range
objects. As a result a range can not have more than 65,535 rows as it is part
of the sheet object.

 

[Rohit Nayak]

I tried something like
Worksheets(SHEET_DIFF_PIVOT).PivotTableWizard
SourceType:=xlConsolidation, SourceData:= _
Array("PivotData!R1C1:R40000C27", "PivotData!R1C31:R2000C57")
This is not working
Is there a row limit on the row within a range?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!