maximum number of rows

tsil

New Member
Joined
Mar 20, 2002
Messages
39
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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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.
 
Upvote 0
sounds like you nead to store your data in access database, then run a quirie to import your data to your worksheet
 
Upvote 0
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
 
Upvote 0
> 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
 
Upvote 0
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
 
Upvote 0
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/InsaneExcel/Default.html

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

Jay
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top