Too Many Rows For Worksheet

[Bright Spark]

I have a mailing list in .txt format that I need to open in Excel in
order to clean up and sort, etc. The problem I am having is that the
list is 76,007 records long and Excel will not allow me to have that
many - I get the message "File not completely loaded" (or something
similar).

I have done some searching and I have discovered that only 65,536 rows
can be in one worksheet (that is exactly how many it will load) but
there does seem to be some way to increase that number as long as the
final number is below 98,304 rows.

I am a total "newbie" as far as Excel is concerned and purely
hunt-and-peck my way through using it, learning as I go, but I cannot
figure out how I can end up with my full 76,007 records in just one
worksheet?

I would appreciate some instruction on this if anyone can help or
point me in the right direction.

Thanks very much.

BrightSpark

 

[Peo Sjoblom]

You either have to put the data in more than one sheet or wait until Excel
2007 is coming out (you can download a beta2 version right now, do a web
search for download beta 2 office 2007), 2007 has 1 million rows and over
16000 columns


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com

 

[Gord Dibben]

The limit per sheet is 65536 rows.

I don't know where the 98,304 came from.

For a VBA method of importing greater than 65536 rows over multiple sheets see

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q120596

NOTE: Excel 2007(beta) has one million rows per sheet.


Gord Dibben MS Excel MVP

 

[Bright Spark]

Wow - 1 million rows! Thanks very much for that information, Peo.

However, this raises one more question for me -- would Excel 2007 be
"backward compatible" - in other words, if I use that to sort and
adjust my mailing list could it be viewed/used by somebody using an
older version of Excel? (The wouldn't be able to see all of the rows
in one worksheet, of course, but at least be able to look at it?)

Thanks again.

BrightSpark

 

[Bright Spark]

Thanks, Gord.

In one of the instruction sets I read, it said "If your worksheet
contains more than 65,536 but fewer than 98,304 rows, use the Text
Import Wizard to import the text file into multiple worksheets" -
that's where I got the 98,304 from, but I realize now when I look at
it that it still needs to go into multiple worksheets.

Is there any way to use Excel 2007 without having to update/include MS
Word? I am so reluctant to do that because everything on my system
always goes haywire when I make any changes to MS Word and it takes me
weeks to put all my settings back together! <sigh>

Thanks again.

Heather

 

[Gord Dibben]

Spark

I have not yet installed the beta version of Office 2007.

There are many regulars in these groups that have.

Hopefully one of them will leap in and address your concern.

But, with versions up to now you could install just the components desired.

Best to wait for more help.


Gord

Thanks, Gord.

In one of the instruction sets I read, it said "If your worksheet
contains more than 65,536 but fewer than 98,304 rows, use the Text
Import Wizard to import the text file into multiple worksheets" -
that's where I got the 98,304 from, but I realize now when I look at
it that it still needs to go into multiple worksheets.

Is there any way to use Excel 2007 without having to update/include MS
Word? I am so reluctant to do that because everything on my system
always goes haywire when I make any changes to MS Word and it takes me
weeks to put all my settings back together! <sigh>

Thanks again.

Heather

Gord Dibben MS Excel MVP

 

[PY & Associates]

If "clean up" means getting rid of blank separators, removing duplicates, we
would suggest

with help of WordPad, paste first 50,000 rows in sheet1, balance in sheet2;
clean up both sheets;
cut and paste sheet2 in sheet1;
remove duplicates again

 

[Peo Sjoblom]

I wouldn't do it since a beta, I use virtual pc and installed the beta
there, otherwise I have had 3 different versions of Office installed on the
same PC without using an virtual hard drives but I wouldn't do it with a
beta. However it seems that MS offer virtual pc for free now!
Once you have installed you need to install a windows OS on the virtual hd.

http://www.microsoft.com/windows/virtualpc/downloads/sp1.mspx


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com

 

[SteveW]

A good starting point

Once sorted, maintain 2 or more sheets to allow for expansion

Steve

 

[Pete_UK]

When you use the Data Import Wizard you can adjust the "Start Import at
Row" value - this is set to 1 by default, but can be increased to 32k
(32768), and with this setting you can then import a further 64k rows
from a text file.

Thus, if your text file contains up to 96k rows (98304) then in theory
you can import all of these, but it must be done in two passes - the
first pass would import rows 1 to 64k into one worksheet and the second
pass would bring in rows 32k to 96k into a second worksheet, with rows
32k to 64k being duplicated in both sheets.

Hope this helps.

Pete

 

[Bright Spark]

Yes, thanks again, Gord -- I am a real "nervous Nellie" when it comes
to adding MS stuff - lol - it's been an ongoing battle for me to keep
it away from other MS stuff that's running just fine as it is - it
always seems to want to "help me out" when I don't want the help!!

Bright Spark

 

[Bright Spark]

Yes, that's what it means - and thank you for the suggestion, I think
that's a start unless I get brave enough to deal with a 1-million line
worksheet!

Bright Spark

 

[Bright Spark]

That's a great idea -- It looks like virtual pc is an excellent way to
go.

Thanks again.

Bright Spark

 

[Bright Spark]

Yes, that seems to be the ticket, I think.

Thanks, Steve.

Bright Spark

 

[Bright Spark]

Thank you, Pete. It's all these blasted Wizards in everything MS that
sometimes makes me think I fell down the rabbit hole - and I keep
forgetting about them and not remembering what they do. Thanks for
this information.

Bright Spark

 

[PY & Associates]

Glad that we can assist.

If you do have a million lines of text, we bet you will have text in
alternate lines only. Perhaps using Win Word, sort, rid of all separator
lines first is in order.

Cheers