Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Deleting unused rows

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    Marion, Illinois
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    New Member
    Join Date
    Feb 2002
    Location
    Marion, Illinois
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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
    [img]/board/images/smiles/icon_smile.gif[/img]
    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.

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ?

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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).

  8. #8
    New Member
    Join Date
    Aug 2007
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

  9. #9
    New Member
    Join Date
    Aug 2007
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •