Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Dec 2000
    Location
    Huntsville AL usa
    Posts
    35
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    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.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

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

    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

  3. #3
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

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

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> 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
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

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

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •