How to delete rows from MS Excel which has >65000 rows?

Question

My Excel sheet seems to have 65555 rows - though we're only using the first few hundred. If I delete rows from the bottom it doesn't seem to change anything. How can I resize it back down to what we're using?

Answer 1

This sometimes happens when those cells have formatting but no data

When you save a workbook, Excel stores only the part of each worksheet that contains data or formatting. Empty cells may contain formatting that causes the last cell in a row or column to fall outside of the range of cells that contains data. This causes the file size of the workbook to be larger than necessary and may result in more printed pages when you print the worksheet or workbook.

Locate and reset the last cell on a worksheet

The most common cause of the last cell being set outside the worksheet range that is currently in use is excessive formatting. When you format whole rows and columns, some types of formatting can cause the last cell to be set to a cell far below or to the right of the actual range that is in use.

How to reset the last cell in Excel

If pressing Ctrl+End brings you far beyond your data range then try resetting the last cells. There are 2 ways to do that

• Manually clear the formatting

  • Select all columns to the right of the last column that contains data, or select all rows below the last row that contains data. A quick way to that is press F5 and type F:IV to delete columns F to IV, or 5:65536 to delete rows 5 to 65536
  • On the Home tab, in the Editing group, click the arrow next to the Clear button then click Clear All.
  • Save the worksheet and close it

• Use VBA

  1. Press Alt+F11 to open VBA

  2. Select Insert > Module, then paste below snippet

     Sub ResetLastCell()
         ActiveSheet.UsedRange
     End Sub
     

  3. Press F5 or click Run

For more information

• Why does my excel document have 960,000 empty rows?
• Ctrl-End doesn't bring me to the last cell
• How to reset last cell in Excel?

Answer 2

As far as Excel is concerned the unused cells don't exist. It doesn't store them in memory and it doesn't save them in the spreadsheet file. So the number of rows could be infinite without causing Excel any problems. I'd guess the limit of 65536 was just a number sufficiently large that Microsoft thought no-one would ever need that many.

That's why your attempts to delete empty rows have no effect, because those rows don't exist anyway.

If you want to hide all the unused rows for cosmetic purposes the usual strategy is to turn off the gridlines so the unused cells just look blank white. Then you can manually format borders around the cells that you are using.

Answer 3

Strange thing. None of the above work for my situation. I have sheet with 43498 rows and when in an unfiltered View the last cell is fine and I have no 'extra' rows below, and the vertical scrollbar behaves as expected, i.e. it will take me down to the last row in the table and no further. But when I am in a filtered view (showing 973 rows) then the vertical scrollbar suddenly thinks there are 85559 rows and the scrollbar becomes for all practical purposes, unusable.

This seems like a different issue, or is it?

[EDIT] Solved, although it remains a strange phenomenon. What worked for me was this answer: https://superuser.com/a/1408570. In short: reset the location of all comments to their natural postition.

Sub ResetComments()
    'Update 20141110
    Dim pComment As Comment
    For Each pComment In Application.ActiveSheet.Comments
        pComment.Shape.Top = pComment.Parent.Top + 5
        pComment.Shape.Left = pComment.Parent.Offset(0, 1).Left + 5
    Next
End Sub

Answer 4

Actually you can't, but select all rows you want to delete then hide them in view tab and save file. Now you won't see them anymore.