0

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?

0

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.

https://support.office.com/en-us/article/Locate-and-reset-the-last-cell-on-a-worksheet-C9E468A8-0FC3-4F69-8038-B3C1D86E99E9

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.

https://web.archive.org/web/20121120150958/http://support.microsoft.com/kb/244435

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 for columns, or 5:65536 for rows, assuming F and 5 is the last column/row
    • 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

  • The latter VBA option does not seem to do anything in Excel 365. – t0mgs Aug 16 '18 at 6:33
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.