Excel Range : Row limit

[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]

Just a thought but is it possible to have the stored procedure update a Table
in MS Access instead of the spreadsheet? If so then you could have the Pivot
table reference the table instead of a range of cells. By referencing the
database Excel has no specific limit that I am aware of. I successfully
linked a Pivot table to over 650,000 records with excellent results.

If not then you can set up a non-contiguious range when you define the raw
data for the pivot table, but as you add more sheets you are going to have to
keep redefining the raw data range...

Hope this helps...

 

[Rohit Nayak]

Thanks for your insight Jim.
I have takent he second option -w eher I split the recordset into
non-contigious blocks of data, each of which can be a max 65 k rows.
However I'm not having any luck in combinin these ranges into one range
that can be given as Source Data for the Pivot. I tried range operations
like Union- but that is not giving me what I want.
So my question to you is -how can I setup the source data of the Pivot
to point to non-contiguous ranges in my worksheet



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