Have 5000 extra rows

[kevs]

I had a file with 800 rows, it now has 6000 blank rows. Is there easy way to
get rid of these? Thanks.

Also at bottom of file I see a tab at bottom says name of file copy.xls,
have no idea why this is there. I click on it and nothing happens.

Thanks.

Kevs



OS 10.4.7
Office 2004

 

[little_creature]

Hello Kevs,
What do you mean by blank rows? Are these the empty rows after your last
cell entry? Excel usually keeps/puts another empty rows after the last
entry, but the number is about 80. If you have more I suspect the workbook
to be corupted. Go to cell A1 and press apple+shift+end what area has been
selected - only the area with filled celles or does it also include some
empty rows/columns after the last entry?

It's not quite clear to me whether the word *copy* is in the file name or
the sheet. The word copy in the file appears when you in finder do Apple+c
and then sunsequently apple+v in the same folder as the original is. I'm
assuming this is your case because if you accidently copy sheet in excel the
Excel names it by number placed in bracket not by copy.

Please give us more information.

 

[Bob Greenblatt]

Kevs,

There are various reasons for this. Most are impossible to determine on a
forensic basis. However, it's pretty easy to fix. First, select a cell, any
cell and press control-END. The selected cell is now where Excel "thinks"
the end of the worksheet is. If it is further to the right and or further
down than where YOU think the end of the worksheet should be, do the
following:

1. Make sure there are no formulas or defined names that refer to cells
beyond your last cell. If so, correct them.
2. Select ALL columns to the right of the column containing your last cell,
and DELETE them.
3. Then select ALL rows below your last cell and DELETE these.
(Note, that this appears to do nothing, as Excel immediately creates new
columns and rows to replace the ones you just deleted. But do it anyway.)
4. Save the workbook.
5. CLOSE the workbook

When you reopen it, everything should be OK. Verify by doing control-END
again.

 

[kevs]

Make sure there are no formulas or defined names that refer to cells
beyond your last cell. If so, correct them.

Thanks Bob, let's do one thing at a time:

"Make sure there are no formulas or defined names that refer to cells
beyond your last cell. If so, correct them."

I don't know what this means.

Actually right now I have 65,000 Rows, which is 64,000 more than I'd like.

(I did paste some new columns in, but how I got 64,000 new black rows making
the file a 500k file to a 4mb file is unimportant for now)



OS 10.4.7
Office 2004

 

[JE McGimpsey]

Actually right now I have 65,000 Rows, which is 64,000 more than I'd like.

(I did paste some new columns in, but how I got 64,000 new black rows making
the file a 500k file to a 4mb file is unimportant for now)

ALL Excel spreadsheets (until XL07/08) have exactly 65536 rows and 256
columns. Not a row less or more. If you delete a row, another is added
to make 65536.

However, XL doesn't display all of them unless there's something in
them, or unless you scroll/page down/otherwise navigate to them.

Bob's suggestion is designed to tell XL to forget about the cells
outside the used range for display purposes.

 

[PhilD]

"Make sure there are no formulas or defined names that refer to cells
beyond your last cell. If so, correct them."

I don't know what this means.

This means that if, say, cell A1 has a formula doing something with a
value in cell A65536, then Excel understands the end of your
spreadsheet as row 65536. Change the formula so that it only refers
to things in the region you want.

Actually right now I have 65,000 Rows, which is 64,000 more than I'd like.

(I did paste some new columns in, but how I got 64,000 new black rows making
the file a 500k file to a 4mb file is unimportant for now)

Ah, if the rows are black then you inserted immediately below a
formatted row, and not knowing any better Excel assumes that you want
the same formatting in every new row, too. That formatting in turn
makes Excel believe that those rows contain stuff you want, and thus
understands the end of your spreadsheet as row 65536. Delete the
formatting.

PhilD

 

[kevs]

This means that if, say, cell A1 has a formula doing something with a
value in cell A65536, then Excel understands the end of your
spreadsheet as row 65536. Change the formula so that it only refers
to things in the region you want.




Ah, if the rows are black then you inserted immediately below a
formatted row, and not knowing any better Excel assumes that you want
the same formatting in every new row, too. That formatting in turn
makes Excel believe that those rows contain stuff you want, and thus
understands the end of your spreadsheet as row 65536. Delete the
formatting.

PhilD

Ok Phil, that's a bit over my head.

JE, and Phil...

Ok, lets make it simple.

What is easy way to tell Excel that I want to keep all cells with stuff in
them, (text), and delete all empty cells, thus reducing the file size by 95%






OS 10.4.7
Office 2004

 

[CyberTaz]

Ok Phil, that's a bit over my head.

JE, and Phil...

Ok, lets make it simple.

What is easy way to tell Excel that I want to keep all cells with stuff in
them, (text), and delete all empty cells, thus reducing the file size by 95%






OS 10.4.7
Office 2004

Hi kevs -

Just thought I'd try to reword a few points for clarification:

First, each Excel worksheet has 256 columns & 65,536 rows. There is nothing
you can do to change that. They can have a Hidden attribute applied to them,
but they are still there. Since Hiding columns & rows doesn't *remove* them
from the sheet, doing so will *not* reduce the size of the file.

Also, from your previous post in this thread:

(I did paste some new columns in, but how I got 64,000 new black rows making
the file a 500k file to a 4mb file is unimportant for now)

Do you mean "black" or is this a typo & you actually meant "blank"? Either
way, you're copying from your browser again, aren't you? If so, you're
probably getting all kind so additional "junk" above & beyond what you
"think" you're copying. I believe *THAT* is what's adding to the size of
your file.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[kevs]

Hi kevs -

Just thought I'd try to reword a few points for clarification:

First, each Excel worksheet has 256 columns & 65,536 rows. There is nothing
you can do to change that. They can have a Hidden attribute applied to them,
but they are still there. Since Hiding columns & rows doesn't *remove* them
from the sheet, doing so will *not* reduce the size of the file.

Also, from your previous post in this thread:


Do you mean "black" or is this a typo & you actually meant "blank"? Either
way, you're copying from your browser again, aren't you? If so, you're
probably getting all kind so additional "junk" above & beyond what you
"think" you're copying. I believe *THAT* is what's adding to the size of
your file.

Regards |:>)
Bob Jones
[MVP] Office:Mac

Thanks Bob, not pasting from browser.
I just opened up a new workbork, and it shows 971 rows. I like it like that.
Now after this issue started, by workbook went from 1 mb to 4 mb.


OS 10.4.7
Office 2004

 

[kevs]

Kevs,

There are various reasons for this. Most are impossible to determine on a
forensic basis. However, it's pretty easy to fix. First, select a cell, any
cell and press control-END. The selected cell is now where Excel "thinks"
the end of the worksheet is. If it is further to the right and or further
down than where YOU think the end of the worksheet should be, do the
following:

1. Make sure there are no formulas or defined names that refer to cells
beyond your last cell. If so, correct them.
2. Select ALL columns to the right of the column containing your last cell,
and DELETE them.
3. Then select ALL rows below your last cell and DELETE these.
(Note, that this appears to do nothing, as Excel immediately creates new
columns and rows to replace the ones you just deleted. But do it anyway.)
4. Save the workbook.
5. CLOSE the workbook

When you reopen it, everything should be OK. Verify by doing control-END
again.

Ok Bob G. tried it and it worked. Thanks.

OS 10.4.7
Office 2004

 

[kevs]

Hi kevs -

Just thought I'd try to reword a few points for clarification:

First, each Excel worksheet has 256 columns & 65,536 rows. There is nothing
you can do to change that. They can have a Hidden attribute applied to them,
but they are still there. Since Hiding columns & rows doesn't *remove* them
from the sheet, doing so will *not* reduce the size of the file.

Also, from your previous post in this thread:


Do you mean "black" or is this a typo & you actually meant "blank"? Either
way, you're copying from your browser again, aren't you? If so, you're
probably getting all kind so additional "junk" above & beyond what you
"think" you're copying. I believe *THAT* is what's adding to the size of
your file.

Regards |:>)
Bob Jones
[MVP] Office:Mac

Yes, meant blank, thanks JE, for reminding me to do what Bob G said, worked.
And always apprecieate other Bob, Cyber, got two great Bobs here!





OS 10.4.7
Office 2004