15

I have an excel document, Office 2007, on a Windows 7 machine (if that part matters any, I'm not sure but just throwing it out there). It is a list of all employee phone numbers. If I need to generate a new page, I can click on page 2 and the table will automatically generate again.

The problem is, someone messed it up since it's on a network drive and now shows I have over 960,000 rows of data, when I really don't! I did CTRL+END to see if any data was in the last cell, so I cleared it out, deleted that row and column, but still didn't fix it. It almost seems like it duplicates itself after the deletion.

How can I fix this instead of recreating the entire document?

CharlieRB
  • 23,021
  • 6
  • 60
  • 107
C-dizzle
  • 1,946

3 Answers3

16

Microsoft has an excellent support document called How to reset the last cell in Excel.

From that document:

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.

Based on the comments below your question, it certainly seems like the border format that is applied to all rows is the culprit.

I've used the code from the add-in provided from the link above to reduce file sizes from multiple megabytes to a few hundred k.

Jon Crowell
  • 2,336
  • 5
  • 26
  • 34
2

Also check to see if there are any cells with comments where the comment box has been dragged down far away from the cell. You can have a cell in the second row of a spreadsheet with a comment whose comment box is near row 7000 and that will force excel to treat row 7000 as the spreadsheet's last row.

ivanatpr
  • 918
1

Like the commenter above - I had this same issue with having over a million extra rows in my excel doc, caused by comments ending up really far from the cell. This page has an easy VBA code you can use to clean up your document, then you can remove it and save the document without the code and the issue will be fixed. https://www.extendoffice.com/documents/excel/2252-excel-reset-comment-positions.html

Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

Click Insert > Module, and paste the following code in the Module Window.

VBA code: Reset all comment positions in active worksheet

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

Then press F5 key to run this code, and all the comments positions in the active worksheet have been reset at once.

Natalie
  • 11