# excess rows in excel



## Ca1Ga1 (Jul 30, 2003)

stupid question time:

I have about 63000 excess rows in excel 2000 that have just junk, but no matter if I delete them or clear them they stay in the worksheet. How do I make them go away?


----------



## CastleHeart (May 4, 2002)

Hey that's not a stupid question! Once I accidentally placed a value in a cell down around 2000. I deleted it but from then on all those blank cells were there and the most annoying thing was that the scroll button was now just a tiny line.

I don't have the cure-all answer and I hope someone else does. But my cure for this annoying spreadsheet was to select the 50 or so rows and columns that encompassed my spreadsheet and copy them and their formats to a new spreadsheet and trash the old one. It's a down and dirty fix but it may be helpful till you find a better one!


----------



## Ca1Ga1 (Jul 30, 2003)

the other rows are still there... I have about 2500 used lines so it thinks I want an extra 63 thousand.


----------



## RandyG (Jun 26, 2000)

So, you outlined the rows you wanted, copied them, and pasted them onto a new worksheet, and it still filles in junk in the remaining 63000 rows??

You didn't just copy the sheet itself, did you?

Are you using some VBA code or Macro in that worksheet?


----------



## CastleHeart (May 4, 2002)

In a new worksheet, moving the scroll bars only (not scroll buttons or with cursor on cells) what is the last cell you see? 
In my Word 2000 it is *M22*.

Strangely enough ... if I click the space behind the scroll bars and let them slam to the end they get smaller and that figure changes to *W40* (a Bill Gates thing) . Either way it is a small sheet size and not mega huge.

As RandyG said, are you copying ONLY the desired cells? Remember, highlighting ten columns or rows by the column or row headings will grab everything there.

- C


----------



## Ca1Ga1 (Jul 30, 2003)

there is vba code behind it (lots of it infact)
and yes, I'm only coping the cells that I want


----------



## CastleHeart (May 4, 2002)

Whoa ... easy now ....  about the copying thing - I wasn't calling you stupid or anything - just checking!

For your info, I inserted a value  w a y  down and over in a Worksheet of mine that is chock full of Macros. The I copied only the main screen's worth over to a new sheet.

All the VBA and data went and yet I didn't get all those blank cells or a large sheet. You never did say.... is your standard worksheet size relatively small? Or does your *EXCEL* start out with a large sheet?

- Castleheart:


----------



## RandyG (Jun 26, 2000)

Is it possible that your code is meant to do calculations and fill in feilds?

If that is the case, then it is probably doing something wrong, filling in all cells, instead of the ones you want.

That's the only reason i can think of if the cells keep getting filled with garbage.


----------



## Ca1Ga1 (Jul 30, 2003)

I didn't mean to be short. I'm at work and having tons of fun already.

when I open excel it starts out fine... its just this project that is huge. 

A pop up box came up that said that it would reset the size if I put the cursor in A1 and saved, but I still 63 thousand... so it got rid of 2000.


----------



## CastleHeart (May 4, 2002)

Every example I have tried on my computer seems to work. I think there may be some validity to the VBA thing. Perhaps you could try the following:


1) choose another spreadsheet of yours that is perhaps large but not too large and do the whole copy/paste thing and see if the results are the same.

2) copy the data from the worksheet in question and PASTE VALUES into a new worksheet. If the size is reduced then perhaps we have narrowed it down to a formula/macro thing.

3) locate your PaperClip office assistant and twist his little head off while shouting, "You want a piece of me Bill Gates!"  

-Castleheart


----------



## Ca1Ga1 (Jul 30, 2003)

Thanks for the ideas / suggestions.

Randy, my code only goes down to line 2700 (which is my last row of data. The cells are blank, but just seem to have junk as I can't get them to go away.

be particular~
Ca1Ga1


----------



## mrwendal (Jul 25, 2001)

Can you try this:

Insert column to left of data (eg so column A is now new column and B is first column of your data)

Autonumber from 1, 2, etc all the way down til the scrollbar hits the bottom.

Sort all columns (including new) by column B (descending). All your 'valid' fields should be grouped together.

Copy these rows only and paste into a new sheet.

On new sheet, highlight all and resort by column A (ascending) So data is in same order as at start, but erroneous rows excluded. Column A can also be deleted.
You can then rename the new sheet to the old name so that the background coding still works (provided you change the old name of course!)

Does this work, or have i got the wrong end of the stick?

NB: Failing this, are you sure there is no value in cell A65536 causing problems?


----------



## Ca1Ga1 (Jul 30, 2003)

Thanks MrWendal,

I can't resort by column be as B would have a number and then the following 3 lines are blank as it has data starting at I for the 4 rows. Resorting this data has proven to be quite a pain in my unmentionables a couple of times.

ie
1 2 3 ....... 12.3
...............12 (this data makes no sense,
................3 but you get the idea)
................25


I can copy paste and it works, but after using the data I'm right back up to the max. I was just hoping that there was some command that would short cut this entire process. 

Thanks for taking the time to answer though. I do appreciate it.

Be particular~
Ca1Ga1


----------



## ptomlin (Sep 25, 2003)

If you accidentally end up with 6000 active rows and a teensy scrollbar, here's how to fix it.

Go the the "end" of the active worksheet: ctrl: End
Select all the rows from that row to the bottom of the work area you want to save. You MUST select the entire row, not just the cells. Then right click on the selected rows and Delete them.

Move to cell A1 and click "Save". 

It will resize the scroll bar.

The problem with copying and pasting to a new worksheet is that if you have active links to other worksheets, you will lose them

Hope this is helpful.
Phil


----------



## Anne Troy (Feb 14, 1999)

2nd paragraph under "Excel prints blank pages":

http://www.theofficeexperts.com/excel.htm#CommonExcelIssues

If you don't want to SEE the blank rows at all under your data, you can select them and hide them.


----------



## XL Guru (Aug 30, 2003)

Phil was pretty much spot-on -- saving the file is the key.

I'd only add in respect of "select all the rows from that row to the bottom", might be easier/safer with CTRL+Shift+DownArrow ... similarly for selecting dead columns, CTRL+Shift+RightArrow.

My only reservation is that there may be something bad in the code that'll put you back to square one before you know it.

Rgds,
Andy


----------

