maximum number of rows
maximum number of rows
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: maximum number of rows

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Hi,
    Does anyone know if it is possible to extend the rows to be more then 65000 ?
    (I am doing some work on an imported data file which sometimes will need about 80 000 rows.)

    regards
    /Tommy

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,797
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-21 09:21, tsil wrote:
    Hi,
    Does anyone know if it is possible to extend the rows to be more then 65000 ?
    (I am doing some work on an imported data file which sometimes will need about 80 000 rows.)

    regards
    /Tommy
    No. Perhaps you should look at Access or any other database system.

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Arkansas
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    sounds like you nead to store your data in access database, then run a quirie to import your data to your worksheet

  4. #4
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Tommy,

    All versions of Excel since at least Excel 95 have 65,536 rows (2^16). Earlier versions had 16,384 (2^14). This cannot be changed.

    For 80K rows, you will either have to:
    a) Use Access or another database
    b) Use Quattro Pro, which can be configured to 1 million rows
    c) Split your data into multiple sheets

    You also may be able to load all the data into a multidimensional VBA array at runtime and then only return to the worksheet what you want (difficult).

    Regards,
    Jay

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You could open your text file and have its contents distributed on multiple worksheets. See http://support.microsoft.com/default...;en-us;Q120596. Obviously, the described macro would need to be updated to reflect Excel's current row limitation -- 65536.

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,797
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    > You also may be able to load all the data into a multidimensional VBA array at runtime and then only return to the worksheet what you want (difficult).

    Jay,

    What would be the performance characteristics of doing it this way?

    Aladin


  7. #7
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    If you know the structure of you data you may be able to re-direct parts of your data to different worksheets.

    Some time back I had a large dataset and imported it with a simple shift-register. I looked for the code to post, but could not find it at the monent.

    My data was comma delineated as a flat file. The import solution was to pull one item at a time. The first item was added to sheet1 and the next item to sheet2 and back again. My data allowed this even and odd import. I do remember that the import took a very long time and the other code which used the data was slowed down considerably as each routine had to pull one row from the first sheet and the next from the other and so on. Some code had problems with the large amount of data, so on additional sheets I built filters for the data and then worked with the reduced filtered data to get around the large data problems. In retrospect no satisfactory solution was ever found to work with the full download? JSW

  8. #8
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    On 2002-03-21 09:33, Aladin Akyurek wrote:
    > You also may be able to load all the data into a multidimensional VBA array at runtime and then only return to the worksheet what you want (difficult).

    Jay,

    What would be the performance characteristics of doing it this way?

    Aladin

    Hi Aladin,

    I really don't know. I would imagine that if kept entirely within VBA it could be pretty quick.

    I am sure it would require some skill that I currently do not have! Some of the array giants would probably need to step in, at least to give me a few pushes.

    Dermot Balson's website
    http://www.webace.com.au/~balson/Ins...l/Default.html

    has some examples where he does everything in a VBA array.

    Jay

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com