Getting rid of the nuisance Rows

[kevs]

I've posted this awhile back but can't get a simple answer to this.

I have a document with 1200 rows.

Below that is 9000 more blank rows.

Hence 90% of the workbook is nuisance rows.

Does ANYBODY, know a simple way to delete these nuisance rows. I can't
figure it out.

And I don't want to 'hide' them. I want them out.

Have no idea why they are there, love to know that too.

Kevs. Thanks.!





OS 10.4.11
Office 2004

 

[CyberTaz]

Not sure exactly what you mean, but...

*All* Excel 2004 worksheets have 65,536 rows. That's how sheets are designed
& there is nothing you can do to change that. Where you come up with 1200
plus only 9000 more (=10,200) I have no idea - that total leaves more than
50,000 rows unaccounted for.

Just as a guess, do you mean there are 1200 rows containing data followed by
9,000 blank rows followed by more rows containing additional data? And
you're looking to remove the empty rows that separate the 2 bodies of
content? If that's the case, you need only select the empty rows [i.e.,
click in the Name Box & type 1201:10200 & press return] then go to Edit>
Delete - which will remove the empty rows from their current location.
However, they just get "tacked on" or repositioned at the bottom of the
sheet below the last occupied row... And you *must* use the Delete command
from the Edit menu, not the [delete] or [del] key on the keyboard. Those
keys simply delete the *content* of selected cells, not the cells/rows.

If this isn't what you need you'll have to be far more explicit about what
you perceive the problem to be.

Again - assuming that the blank rows separate rows of content - there are
only two likely reasons why the blank rows are there:

1- Someone typed the data in & skipped 9,000 rows between one group of data
& the next, or

2- The data was copied from a source that included [hidden] information that
caused the rows to be skipped (or filled with blank space) when pasted.

Either way you should be able to remove them as indicated above.

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

 

[Mike Middleton]

kevs -

http://www.contextures.com/xlfaqApp.html#Unused

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

 

[kevs]

Not sure exactly what you mean, but...

*All* Excel 2004 worksheets have 65,536 rows. That's how sheets are designed
& there is nothing you can do to change that. Where you come up with 1200
plus only 9000 more (=10,200) I have no idea - that total leaves more than
50,000 rows unaccounted for.

Just as a guess, do you mean there are 1200 rows containing data followed by
9,000 blank rows followed by more rows containing additional data? And
you're looking to remove the empty rows that separate the 2 bodies of
content? If that's the case, you need only select the empty rows [i.e.,
click in the Name Box & type 1201:10200 & press return] then go to Edit>
Delete - which will remove the empty rows from their current location.
However, they just get "tacked on" or repositioned at the bottom of the
sheet below the last occupied row... And you *must* use the Delete command
from the Edit menu, not the [delete] or [del] key on the keyboard. Those
keys simply delete the *content* of selected cells, not the cells/rows.

If this isn't what you need you'll have to be far more explicit about what
you perceive the problem to be.

Again - assuming that the blank rows separate rows of content - there are
only two likely reasons why the blank rows are there:

1- Someone typed the data in & skipped 9,000 rows between one group of data
& the next, or

2- The data was copied from a source that included [hidden] information that
caused the rows to be skipped (or filled with blank space) when pasted.

Either way you should be able to remove them as indicated above.

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

I've posted this awhile back but can't get a simple answer to this.

I have a document with 1200 rows.

Below that is 9000 more blank rows.

Hence 90% of the workbook is nuisance rows.

Does ANYBODY, know a simple way to delete these nuisance rows. I can't
figure it out.

And I don't want to 'hide' them. I want them out.

Have no idea why they are there, love to know that too.

Kevs. Thanks.!





OS 10.4.11
Office 2004

Thanks Bob:
I tried what you said, but they still wont disappear. Here is a screenshot:

http://im
g139.imageshack.us/img139/579/picture1gq0.th.png



Or
http://img139.imageshack.us/img139/579/picture1gq0.png


As you can see from scoll bar, this file has thousands sof blank rows. There
are quite a nuiscance.

What am I leaving out?

Thanks as always.






OS 10.4.11
Office 2004

 

[CyberTaz]

You still haven't clarified the point made in my prior reply... The screen
shot doesn't answer the question - and if you're going to post links to web
sites it's not appreciated that they trigger pop-ups & commercial trickery.

Is There Any More Data BEYOND what you refer to as the "nuisance rows"???

If *not* you CANNOT make the blank rows simply "disappear" unless you choose
to Hide them or do something weird like changing their border color to white
or changing their fill color to black or applying some other color as both
border & fill. That, however, still doesn't take them away - it just makes
them less obvious.

This was stated by several responders including myself when you brought up
the same question before. If, for example, you have data in rows 1 through
10,000 and you try to delete any rows from 10,001- 65,536 they immediately
get reinserted - making it seem as though they didn't get deleted & there
isn't anything you can do to prevent that. Excel worksheets are designed to
provide 65,536 rows - no more, no less - period.

OTOH, if you have data in rows 1 - 10,000 *and* in rows 11,000 -11,500 you
*can* select & delete rows 10,001 - 10,999 in order to "close the gap"
between the first group of data & the other. IOW, the data in rows 11,000 -
11,500 shifts up to row 10,001 - 10,501... But the empty rows you deleted
still get "tacked on" at [moved to] the bottom of the sheet leaving you with
the full complement of 65,536 rows.

BTW - From the shot you posted it looks like you already have rows 2 through
2679 hidden - intentionally or otherwise.

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

Not sure exactly what you mean, but...

*All* Excel 2004 worksheets have 65,536 rows. That's how sheets are designed
& there is nothing you can do to change that. Where you come up with 1200
plus only 9000 more (=10,200) I have no idea - that total leaves more than
50,000 rows unaccounted for.

Just as a guess, do you mean there are 1200 rows containing data followed by
9,000 blank rows followed by more rows containing additional data? And
you're looking to remove the empty rows that separate the 2 bodies of
content? If that's the case, you need only select the empty rows [i.e.,
click in the Name Box & type 1201:10200 & press return] then go to Edit>
Delete - which will remove the empty rows from their current location.
However, they just get "tacked on" or repositioned at the bottom of the
sheet below the last occupied row... And you *must* use the Delete command
from the Edit menu, not the [delete] or [del] key on the keyboard. Those
keys simply delete the *content* of selected cells, not the cells/rows.

If this isn't what you need you'll have to be far more explicit about what
you perceive the problem to be.

Again - assuming that the blank rows separate rows of content - there are
only two likely reasons why the blank rows are there:

1- Someone typed the data in & skipped 9,000 rows between one group of data
& the next, or

2- The data was copied from a source that included [hidden] information that
caused the rows to be skipped (or filled with blank space) when pasted.

Either way you should be able to remove them as indicated above.

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

I've posted this awhile back but can't get a simple answer to this.

I have a document with 1200 rows.

Below that is 9000 more blank rows.

Hence 90% of the workbook is nuisance rows.

Does ANYBODY, know a simple way to delete these nuisance rows. I can't
figure it out.

And I don't want to 'hide' them. I want them out.

Have no idea why they are there, love to know that too.

Kevs. Thanks.!





OS 10.4.11
Office 2004

Thanks Bob:
I tried what you said, but they still wont disappear. Here is a screenshot:

http://im
g139.imageshack.us/img139/579/picture1gq0.th.png



Or
http://img139.imageshack.us/img139/579/picture1gq0.png


As you can see from scoll bar, this file has thousands sof blank rows. There
are quite a nuiscance.

What am I leaving out?

Thanks as always.






OS 10.4.11
Office 2004

 

[kevs]

You still haven't clarified the point made in my prior reply... The screen
shot doesn't answer the question - and if you're going to post links to web
sites it's not appreciated that they trigger pop-ups & commercial trickery.

Is There Any More Data BEYOND what you refer to as the "nuisance rows"???

If *not* you CANNOT make the blank rows simply "disappear" unless you choose
to Hide them or do something weird like changing their border color to white
or changing their fill color to black or applying some other color as both
border & fill. That, however, still doesn't take them away - it just makes
them less obvious.

This was stated by several responders including myself when you brought up
the same question before. If, for example, you have data in rows 1 through
10,000 and you try to delete any rows from 10,001- 65,536 they immediately
get reinserted - making it seem as though they didn't get deleted & there
isn't anything you can do to prevent that. Excel worksheets are designed to
provide 65,536 rows - no more, no less - period.

OTOH, if you have data in rows 1 - 10,000 *and* in rows 11,000 -11,500 you
*can* select & delete rows 10,001 - 10,999 in order to "close the gap"
between the first group of data & the other. IOW, the data in rows 11,000 -
11,500 shifts up to row 10,001 - 10,501... But the empty rows you deleted
still get "tacked on" at [moved to] the bottom of the sheet leaving you with
the full complement of 65,536 rows.

BTW - From the shot you posted it looks like you already have rows 2 through
2679 hidden - intentionally or otherwise.

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

Not sure exactly what you mean, but...

*All* Excel 2004 worksheets have 65,536 rows. That's how sheets are designed
& there is nothing you can do to change that. Where you come up with 1200
plus only 9000 more (=10,200) I have no idea - that total leaves more than
50,000 rows unaccounted for.

Just as a guess, do you mean there are 1200 rows containing data followed by
9,000 blank rows followed by more rows containing additional data? And
you're looking to remove the empty rows that separate the 2 bodies of
content? If that's the case, you need only select the empty rows [i.e.,
click in the Name Box & type 1201:10200 & press return] then go to Edit>
Delete - which will remove the empty rows from their current location.
However, they just get "tacked on" or repositioned at the bottom of the
sheet below the last occupied row... And you *must* use the Delete command
from the Edit menu, not the [delete] or [del] key on the keyboard. Those
keys simply delete the *content* of selected cells, not the cells/rows.

If this isn't what you need you'll have to be far more explicit about what
you perceive the problem to be.

Again - assuming that the blank rows separate rows of content - there are
only two likely reasons why the blank rows are there:

1- Someone typed the data in & skipped 9,000 rows between one group of data
& the next, or

2- The data was copied from a source that included [hidden] information that
caused the rows to be skipped (or filled with blank space) when pasted.

Either way you should be able to remove them as indicated above.

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



On 12/28/07 9:36 PM, in article C39AF2C3.2FA18%[email protected], "kevs"

I've posted this awhile back but can't get a simple answer to this.

I have a document with 1200 rows.

Below that is 9000 more blank rows.

Hence 90% of the workbook is nuisance rows.

Does ANYBODY, know a simple way to delete these nuisance rows. I can't
figure it out.

And I don't want to 'hide' them. I want them out.

Have no idea why they are there, love to know that too.

Kevs. Thanks.!





OS 10.4.11
Office 2004

Thanks Bob:
I tried what you said, but they still wont disappear. Here is a screenshot:

http://im
g139.imageshack.us/img139/579/picture1gq0.th.png



Or
http://img139.imageshack.us/img139/579/picture1gq0.png


As you can see from scoll bar, this file has thousands sof blank rows. There
are quite a nuiscance.

What am I leaving out?

Thanks as always.






OS 10.4.11
Office 2004

Ok Bob, I just opened up an old Excel file that has 250 rows and only first
200 are being used. What did I do to achieve that?

I have many workbooks like that-- they are harmonious. 500 rows, only
first 400 filled up.

Now this one, I don't know what, you touch the scroll bar just a bit, and
boom-- you are at row 5000, instead of row 1800. It's a real problem.

Sorry bout screen shot pop up. That not me that's Image Shack , although the
second version did not have that.

This is way over my head, but the gist of what you are saying I think is
just let it go -- there is not easy way to have it the ideal way I like it.

To my knowledge there is nothing in those other 9000 rows or so.

Kevs

OS 10.4.11
Office 2004

 

[Mike Middleton]

Kevs -

Your problem with the big jumps of the scroll bar might be solved by the
steps described in "Why do my scrollbars go to row 500 -- my data ends in
cell E50?" at

http://www.contextures.com/xlfaqApp.html#Unused

(This is the same solution I proposed in my newsgroup message of Dec. 29,
2007.)

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

 

[PhilD]

Ok Bob, I just opened up an old Excel file that has 250 rows and only first
200 are being used. What did I do to achieve that?

I have many workbooks like that-- they are harmonious.   500 rows, only
first 400 filled up.

Now this one, I don't know what, you touch the scroll bar just a bit, and
boom-- you are at row 5000, instead of row 1800. It's a real problem.

Two things.

1) You still haven't clarified CyberTaz's question, so no one can
help you totally fully.

2) I suspect that you have the bottom set of rows "hidden" without
realising it. As others keep saying, there are always without fail
65536 rows in a sheet.

PhilD

 

[marco.araujo]

Hi folks,

I'm having the same problem as kevs, and hopefully I can clarify
what's going on by detailing a symptom. I don't have a solution, but
please read on and see if this clears up what the issue is.

I'm using a worksheet as the data source in a Word mailmerge. I move
through the process, and when I'm given the option to exclude some
recipients (window pops up and you uncheck the box that corresponds to
each row you want to exclude), I have extra empty rows at the bottom.
In kevs's case, this would mean that there are 9000 rows at the bottom
that he would have to uncheck and exclude if he does not want to deal
with 9000 extra blank documents. If her were able to "delete" those
9000 rows, they would not appear in that little pop-up window.

See, what kevs was trying to demonstrate with the screenshot is that
when you reach the bottom of your data set and enter the land of empty
rows, the vertical scroll bar on the right is typically at the bottom
of its "track". In the screenshot he supplied, the vertical scroll bar
is in the middle of its "track" even though it has reached the bottom
of the data set.

Does that make things clear? If so, any ideas? I'm as stumped as he
is.

Thanks everyone...

 

[marco.araujo]

kevs,

If nothing else, I just figured out a workaround that should have been
obvious to me all along. Slapping my forehead...

1) Add a new sheet to your workbook.

2) Highlight and copy your 1200 rows of data.

3) Paste them into the new sheet.

You'll lose the column spacing and frozen panes, but that's simple
formatting. All the data's there, and none of the nuisance rows.

Good?

 

[Mike Middleton]

marco.araujo & kevs -

... I'm as stumped as he is. ... <

I also am baffled. I have proposed a solution twice in this thread, but I
haven't seen any indication from marco.araujo or kevs that my proposed
solution has been tried (or that it's a solution to the wrong problem or
it's a solution that doesn't work, or ...). So, for the third time, here is
a link to Debra Dalgleish's solution:

http://www.contextures.com/xlfaqApp.html#Unused

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

 

[marco.araujo]

marco.araujo & kevs -


I also am baffled. I have proposed a solution twice in this thread, but I
haven't seen any indication from marco.araujo or kevs that my proposed
solution has been tried (or that it's a solution to the wrong problem or
it's a solution that doesn't work, or ...). So, for the third time, here is
a link to Debra Dalgleish's solution:

http://www.contextures.com/xlfaqApp.html#Unused

- Mike Middletonhttp://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

Hey Mike,

Thanks for offering the solution, but no, it unfortunately didn't
work. I tried it before I wrote my posts, and what happens is
something that CyberTaz already addressed. Namely, the deleted rows
are simply appended to the bottom of the spreadsheet, and there's
nothing apparent to be done about that:

"they immediately get reinserted - making it seem as though they
didn't get deleted & there
isn't anything you can do to prevent that"

I don't know... I'm satisfied with the workaround, and hopefully kevs
will be, too...

Thanks all...

 

[CyberTaz]

Hi Marco -

Thanks for your contribution

Actually, the issue is rather clear - at least the cause. If you review my
first reply to kevs' op, item #2 refers to indiscriminant acquisition of
data - sometimes importing from a corrupted source but more often copying
from another file or web page. It deceives Excel into believing that there
actually is content in the empty cell or that they are a part of the defined
data range. Thus it includes them as records in operations such as mail
merge, such as you describe in your situation.

In most cases that can be resolved by the methods Mike pointed to and may
only require selecting & deleting the "empty" rows. Despite the fact that
they can't be physically discarded from the sheet, it puts Excel back into
recognition that they no longer have content. Occasionally, however, the
nature of the acquired content (stuff above & beyond the data you think
you're getting) is rancid enough to corrupt the sheet & those techniques may
not be able to remedy it. In those cases the solution in your later post is
one of the usual ways to address the problem - So, Good For You!

However, there's no need for you to lose any formatting - including column
widths - when you paste the data to a new sheet. Explore the capabilities of
Edit> Paste Special in order to avoid that loss, keeping in mind that once
you paste you can go to that feature & paste again using a different option.
[Note: Freeze Panes doesn't get included because it is a feature, not a
formatting attribute.]

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

 

[Bob Greenblatt]

Debra's solution will always work! If it does not, you are doing something
wrong. Remember that you MUST SAVE and CLOSE the file after deleting the
unused rows and columns. When the file is reopened, it will have the correct
"last cell" assuming you deleted the proper rows and columns. You might also
look in the defined names to be sure that there is not a reference to the
"empty" cells.

I'm with Mike here and not at all confident that you and Kevs have followed
the solution steps properly.

 

[marco.araujo]

CyberTaz -

Yeah, I imported my data from Raiser's Edge fundraising software, and
it wouldn't surprise me at all to find out I imported some gremlins
along with my data.

Thanks for your insight and the Paste Special tip - I'll certainly use
it...