Deleting unused rows

mabsher

New Member
Joined
Feb 16, 2002
Messages
19
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?
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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
 
Upvote 0
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
:)
 
Upvote 0
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 <End><Home>, 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.
 
Upvote 0
On 2002-02-26 11:55, Chris Davison wrote:
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
/board/images/smiles/icon_smile.gif

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.
 
Upvote 0
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 ?
 
Upvote 0
On 2002-02-26 13:07, Chris Davison wrote:
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 ?

Yes, Paste affects both the target cell's contents and format. Paste Special... provides more options (e.g., you can copy Formulas only).
 
Upvote 0
same problem - but with columns

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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top