65K row limit (Excel 2002 / SP-2)

Topic

New Reply

We use Excel on Win2000 SP4 to crunch raw data for analysis. We really use the Excel filters and plots to a great advantage. Now we are bumping into the 65,000 row limit of Excel.
Does anyone know how we can increase the rows to say 500,000 rows? We checked out the XP version but it appears to have the same row limit. Since we look through many datafiles, breaking the files into pieces and recombining the results by hand is not a good solution. Any ideas??
Thanks in advance.

Reply | Quote

Replies

You can use access which (I think) is only memory limited
or you can use multiple sheets as extensions of the list, but you must write routines to keep them straight.

In VB you could combine them into arrays to work with them, though I do not know the limitations of the VB arrays.

Steve

Reply | Quote

You can use access which (I think) is only memory limited
or you can use multiple sheets as extensions of the list, but you must write routines to keep them straight.

In VB you could combine them into arrays to work with them, though I do not know the limitations of the VB arrays.

Steve

Reply | Quote

Dan

Sorry You can not!!! All Excel can handle is 65536 which is 2 to the power of ??? you do the math.

Now there was a glitch in the way the columns, 256 again 2 to the power of ???, when you publish to the web, and it will actually go to ZZ columns, that is from A to AB, to ZZ. Currently it only handles IV which is 256. But when you brought the workbook back to Excel all the extra columns were gone, and truncated.

Best way I can think of working around it, is via a Database and ADO, or text file that can be read into once you need to work with the data.

Maybe you can use multiple worksheets, but again these are solutions that you can try and figure out which one will work best.

Hope this helps.

Wassim

Reply | Quote

Dan

Sorry You can not!!! All Excel can handle is 65536 which is 2 to the power of ??? you do the math.

Now there was a glitch in the way the columns, 256 again 2 to the power of ???, when you publish to the web, and it will actually go to ZZ columns, that is from A to AB, to ZZ. Currently it only handles IV which is 256. But when you brought the workbook back to Excel all the extra columns were gone, and truncated.

Best way I can think of working around it, is via a Database and ADO, or text file that can be read into once you need to work with the data.

Maybe you can use multiple worksheets, but again these are solutions that you can try and figure out which one will work best.

Hope this helps.

Wassim

Reply | Quote

FYI, I just did a test and I was able to create an array in VB with over 1 million elements and could fill it with 11 complete columns (720,896 items) of info (in one column) in a little over 3 seconds: so manipulation of multiple sheets in VB is feasible to convert to a “large sheet” in code.

I was also able to make an over 14 million element 2-D array (1,000,000 rows x 14 columns) without any problems (I did get a problem with Memory when I had 1,000,000 x 15 so there is a limit in memory.)

Steve

Reply | Quote

FYI, I just did a test and I was able to create an array in VB with over 1 million elements and could fill it with 11 complete columns (720,896 items) of info (in one column) in a little over 3 seconds: so manipulation of multiple sheets in VB is feasible to convert to a “large sheet” in code.

I was also able to make an over 14 million element 2-D array (1,000,000 rows x 14 columns) without any problems (I did get a problem with Memory when I had 1,000,000 x 15 so there is a limit in memory.)

Steve

Reply | Quote