exceeding 65536 rows?

[Jeff]

Using Excel 2002 and have discovered that there is a limit of 65536 rows.
Is there a way to exceed this number of rows in a single spreadsheet?

 

[T. Valko]

In a word, no.

Excel 2007 has been expanded with more than 1 million rows and more than 16K
columns.

 

[Ben M. Schorr - MVP (OneNote)]

Yes. Upgrade to Excel 2007.

--
-Ben-
Ben M. Schorr, MVP
Roland Schorr & Tower
http://www.rolandschorr.com
http://www.officeforlawyers.com

 

[Jeff]

Thanks, though that is a expensive way to do it........

I wonder if the on-line Excel (or maybe the free spreadsheets) can go beyond
65,536.

Jeff

 

[BobS]

Using Excel 2002 and have discovered that there is a limit of 65536 rows.
Is there a way to exceed this number of rows in a single spreadsheet?

Jeff,

No you can't not without upgrading to Excel 2007. But if you can live with
multiple sheets having up to 65,536 rows each then there is a solution.

I just did this for a project that has over 400,000 records in a 24hr log
file. This routine imports the records and automatically adds sheets as
needed. Since you cannot use more than 65,536 rows per sheet your only
choice is to upgrade to Excel 2007 or to use multiple sheets.

The original subroutine was written by Chip Pearson and can be found here:

http://www.cpearson.com/excel/ImportBigFiles.htm

ImportBigFiles is the routine you want along with the accompanying
Function - IsFileOpen to verify that the file you're opening is not in-use.

His routines work nicely and the documentation he includes is like reading a
"How To" manual - excellent. These routines are now embedded in the project
workbook which will run without any changes in both Excel 2003 & 2007. So if
you do upgrade later, it will still work.

Bob S.

 

[Jeff]

Jeff,

No you can't not without upgrading to Excel 2007. But if you can live
with multiple sheets having up to 65,536 rows each then there is a
solution.

I just did this for a project that has over 400,000 records in a 24hr log
file. This routine imports the records and automatically adds sheets as
needed. Since you cannot use more than 65,536 rows per sheet your only
choice is to upgrade to Excel 2007 or to use multiple sheets.

The original subroutine was written by Chip Pearson and can be found
here:

http://www.cpearson.com/excel/ImportBigFiles.htm

ImportBigFiles is the routine you want along with the accompanying
Function - IsFileOpen to verify that the file you're opening is not
in-use.

His routines work nicely and the documentation he includes is like reading
a "How To" manual - excellent. These routines are now embedded in the
project workbook which will run without any changes in both Excel 2003 &
2007. So if you do upgrade later, it will still work.

Bob S.

Thanks Bob. That is very helpful and I will explore it further.

I downloaded OpenOffice to see what it had but it too is limited to 65,536
rows, so it did not help.

Jeff

 

[Pete_UK]

Quattro Pro (part of Word Perfect Office from Corel) has had 1 million
row capability for many years - it is significantly cheaper than Excel
2007, so this might be a possible way forward for you.

Hope this helps.

Pete

 

[Ben M. Schorr - MVP (OneNote)]

...Google's spreadsheets, by the way, are limited to 10,0000 rows.

--
-Ben-
Ben M. Schorr, MVP
Roland Schorr & Tower
http://www.rolandschorr.com
http://www.officeforlawyers.com

 

[Jeff]

Guess that knocks them out......

Jeff

..Google's spreadsheets, by the way, are limited to 10,0000 rows.

--
-Ben-
Ben M. Schorr, MVP
Roland Schorr & Tower
http://www.rolandschorr.com
http://www.officeforlawyers.com

 

[Jeff]

Thank you Pete. You are very helpful. I have an old version of Quattro Pro
somewhere. I'll dig it up and see.

Thanks.

Quattro Pro (part of Word Perfect Office from Corel) has had 1 million
row capability for many years - it is significantly cheaper than Excel
2007, so this might be a possible way forward for you.

Hope this helps.

Pete

 

[Bill Ridgeway]

Using Excel 2002 and have discovered that there is a limit of 65536 rows.
Is there a way to exceed this number of rows in a single spreadsheet?

If you have that much information it looks as though you have a database
rather than a spreadsheet. In that case Access (or other database manager)
would be more appropriate and, I am sure, this doesn't have a limit on
capacity - although you may, eventually, come up against a limitation of the
system itself.

Bill Ridgeway

 

[Jeff]

If you have that much information it looks as though you have a
database rather than a spreadsheet. In that case Access (or other
database manager) would be more appropriate and, I am sure, this
doesn't have a limit on capacity - although you may, eventually, come
up against a limitation of the system itself.

Bill Ridgeway

Thanks Bill.

Actually the data is a tab delimited txt file and I open it in in Excel for
one reason only: because whenever I add new rows of data I then need to sort
it based on the first column. If I could find a way to sort a tab delimited
txt file in some other way I would not need to open it in Excel.

I am not familiar with using Access even though I do have Access 2002 as
part of the suite and am concerned that using it might involve a prolonged
learning process.

Jeff

 

[Harlan Grove]

I wonder if the on-line Excel (or maybe the free spreadsheets) can go beyond
65,536.

....

On-line Excel? Office Live? I think you need to have an Office 2007
license to use it.

The OWC spreadsheet object provides more rows, but using it isn't like
using Excel.

As for the free spreadsheets, OpenOffice Calc 2.4 only provides 65536
rows, and it's not easy to modify sources and rebuild. Gnumeric is
easier to modify and rebuild, but still not exactly easy for
nonprogrammers. The free on-line spreadsheets only provide 65536 or
fewer rows.

 

[Jeff]

...

On-line Excel? Office Live? I think you need to have an Office 2007
license to use it.

The OWC spreadsheet object provides more rows, but using it isn't like
using Excel.

As for the free spreadsheets, OpenOffice Calc 2.4 only provides 65536
rows, and it's not easy to modify sources and rebuild. Gnumeric is
easier to modify and rebuild, but still not exactly easy for
nonprogrammers. The free on-line spreadsheets only provide 65536 or
fewer rows.

Thank you.

Jeff