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?
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.
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.
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
- Press Alt+F11 to open VBA
Select Insert > Module, then paste below snippet
Sub ResetLastCell() ActiveSheet.UsedRange End Sub
Press F5 or click Run
For more information
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.