maximum number of rows

tsil · Mar 21st, 2002, 10:21 AM

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

Aladin Akyurek · Mar 21st, 2002, 10:23 AM

Quote:

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.

dantb · Mar 21st, 2002, 10:27 AM

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

Jay Petrulis · Mar 21st, 2002, 10:29 AM

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

Mark W. · Mar 21st, 2002, 10:30 AM

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.

Aladin Akyurek · Mar 21st, 2002, 10:33 AM

> 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

Joe Was · Mar 21st, 2002, 11:21 AM

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

Jay Petrulis · Mar 21st, 2002, 12:21 PM

Quote:

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 21st, 2002, 10:21 AM	#1
tsil New Member Join Date: Mar 2002 Posts: 33	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

Mar 21st, 2002, 10:27 AM	#3
dantb Board Regular Join Date: Mar 2002 Location: Arkansas Posts: 358	sounds like you nead to store your data in access database, then run a quirie to import your data to your worksheet

Mar 21st, 2002, 10:29 AM	#4
Jay Petrulis MrExcel MVP Join Date: Mar 2002 Location: Chicago, IL USA Posts: 2,042	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

Mar 21st, 2002, 10:30 AM	#5
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	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.

Mar 21st, 2002, 10:33 AM	#6
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	> 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

Mar 21st, 2002, 11:21 AM	#7
Joe Was MrExcel MVP Join Date: Feb 2002 Location: Central Florida, USA Posts: 7,541	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