mabsher

I can't remember the shortcut method to deleting all unused rows in a spreadsheet. Somehow my sheet shows all 65xx rows and I only used 85.

Help?

Mark W.

Use the Edit | Go To... | Special... Last Cell menu command. Delete all rows from the selected cell up to the last row (85?) containing data and Save your worksheet.

[ This Message was edited by: Mark W. on 2002-02-26 06:50 ]

Chris Davison

Hi Mark,

often, when I use CNTRL-V to paste and the arrowdown key, ie pasting to the next cell down, say, 10 times quickly as it's quicker than selecting the range, I mess up the key order and end up on row 65,536, pasting the data/formula there.... so I just delete it and return

does the "last cell" therefore become this row 65,536 cos I accessed it ? Would this affect the filesize too if I saved without deleting the rows inbetween my kosher data and my row 65,536 indiscretion ?

(I'm often surprised by some of my filesizes sometimes)

cheers
Chris

mabsher

Mr Excel gave me this fix. It seems to work best:

Let's say row 65 is the last row you want to keep:

Method 1:
Click on the gray "66" box to the left of cell A66.
Use the scrollbar to go to the bottom of the sheet. Hold down the shift key, click the grey 65536 box to the left of A65536. This should select all of these rows. Now, right click the row number and choose Delete.

You then have to save the file, close the file, open the file. When you hit , the last active row should now be row 65.

Method 2:
Go to VBA editor. Hit Ctrl+G to open immediate window. In the immediate window, type:
Range("A66:A65536").EntireRow.Delete

Save the file, close the file, open the file.

Mark W.

The memory extent of a worksheet (and its file size when saved) is increased when formats are applied to empty cells. This is repaired by the deletion of complete rows/columns beyond the last cell containing a value or formula. The Edit | Go To... | Special... Last cell command identifies what Excel "thinks" is the last cell. Deleting "phantom" rows/columns and re-Saving the worksheet resets the memory extent.

Chris Davison

thanks Mark

I wonder if CNTRL C / CONTRL V which just copy pastes *does* actually apply a format per that help note.... if I'm just copying a formula, am I also unknowingly pasting a default cell format... do you know what I mean ?

rephrase

if I copy / paste a default format (by dint of the fact that I was copy/pasting a formula) to my 65,536 row cell, will it be interpreted as a format change per that help note ?

Mark W.

Yes, Paste affects both the target cell's contents and format. Paste Special... provides more options (e.g., you can copy Formulas only).

karas

this is my first visit to this website (googled the problem) and i have already sorted the unused rows problem. thank you very much. in my case the vba method worked.

unfortunatley i have the same problem with columns, one of the tabs suddenly acquired columns going to IV (this is together with rows goign beyong 65k) and i cannot get rid of the unused columns. tried using the same logic and puttig this message in vba immediate window:

Range("BO302:IV302").EntireColumn.Delete

your help would be much much appreciated
thanks.

karas

just to add: im doing this to minimize the size of the file & speed up calculations.

triming rows from 65+ to 300 has knocked off 18 megs and im currently down to just over 1 meg. can i achieve the same results with deleting unused columns?